SQL for QA: Tutorial#13 – Stored Functions
Testing team uses Stored Functions very often to test complex functionalities, mainly where lot of calculations are involved. It is easier to use functions for it than writing long query every time. Banking domain is the one where Testers need to use stored Functions in database testing.
But don’t worry, most of the time DB team or developers help QAs to write those functions. QA team only need to prepare test data carefully to pass as an parameter to functions and verify results.
In this article we will see how Functions are created and executed using PostgresSQL.
User defined Functions in PostgresSQL is same like the functions in other programming languages, it is called as Stored Functions. Stored function and its calling code are both written in PL/pgSQL.
Adavantage Of Stored Functions:
In plain SQL, it is not possible to write complex functions, so these procedural languages add many procedural elements like control structures, loop, and complex calculation which extend SQL-standard. It allows you to develop complex user defined functions and stored procedures in PostgreSQL.
Disadavantage Of Stored Functions:
A disadvantage of user-defined functions in PostgresSQL is that, they cannot execute transactions, in short inside a function you cannot open a new transaction, even commit or rollback the current transaction.
Transactions can be executed with Stored Procedures, But PostgresSQL only supports stored function not stored procedures (Oracle supports Procedures and PostgreSQL 11 introduced stored procedures that support transactions.)
Create User-defined Function in PostgreSQL
By using CREATE FUNCTION we can define a new function. CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition.
|
1 2 3 4 5 6 7 |
CREATE FUNCTION <Function_Name>(Parameter_1 datatype,Parameter_2 datatype) RETURNS <return datatype> AS BEGIN Write your logic here END; LANGUAGE language_name; |
- Function_Name: is the name of the function, write after CREATE FUNCTION keywords
- Parameters: After Function_Name you can give list of parameters and their datatypes separated by comma inside parentheses.
- Then write down return data type of the function after the RETURNS keyword.
- Code or logic is placed inside the BEGIN and END block. The function always ends with a semicolon (;) followed by the END keyword.
- Finally, specify the procedural language of the function e.g., plpgsql in case PL/pgSQL is used.
Example 1: Function to add 2 integer numbers
|
1 2 3 4 5 6 7 8 |
CREATE FUNCTION Add_1(Value_1 integer, Value_2 integer) RETURNS integer AS $$ DECLARE Answer integer; BEGIN Answer= Value_1 + Value_2; RETURN Answer; END; $$ LANGUAGE PLPGSQL; |
Execute Function:
|
1 |
SELECT Add_1(20,20); |
Output:
|
1 2 3 |
Add_1 ----- 40 |
Example 2: Function to display employee count from given table.
|
1 2 3 4 5 6 7 8 9 10 |
CREATE OR REPLACE FUNCTION totalRecords1 () RETURNS integer AS $total$ DECLARE total integer; BEGIN SELECT count(*) into total FROM emp_view; RAISE NOTICE 'No of employees =%', total; RETURN total; END; $total$ LANGUAGE plpgsql; |
Execute Function:
|
1 |
select TotalRecords1(); |
Output Of Function:
|
1 2 3 |
TotalRecords1 -------------- 2 |
Example 3: function to find out factorial of a number
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE OR REPLACE FUNCTION Cal_Fact( i integer) RETURNS integer AS $$ BEGIN IF i=0 THEN RETURN 1; ELSIF i=1 THEN RETURN 1; ELSE RETURN i*Cal_Fact(i-1); END IF; END; $$ LANGUAGE plpgsql; |
Execute Function:
|
1 |
SELECT Cal_Fact(5); |
Output Of Function:
|
1 2 3 |
Cal_Fact --------- 120 |
Example 4: Function which returns a table
Consider a following employee table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE OR REPLACE FUNCTION get_emp (e_pattern VARCHAR,sal INT) RETURNS TABLE ( ename VARCHAR, sala INT) AS $$ DECLARE var_r record; BEGIN FOR var_r IN (SELECT e_name , e_sal FROM employee WHERE e_name ILIKE e_pattern AND e_sal >= sal) LOOP ename := upper(var_r.e_name) ; sala := var_r.e_sal; RETURN NEXT; END LOOP; END; $$ LANGUAGE 'plpgsql'; |
Execute Function:
|
1 |
SELECT get_emp('%ta', 10000); |
Output Of Function:
|
1 2 3 4 |
Get_Emp Record --------------- (ANITA,10000) (NITA,80000) |
Some Examples on PL/pgSQL function parameters: IN, OUT, INOUT and VARIADIC.
By default, any parameter in PostgreSQL is IN parameter. We can pass the IN parameters to the function but you cannot get them back as a part of the result.
OUT parameter:
The OUT parameters are defined as part of the function arguments list and are returned back as a part of the result. PostgreSQL supported the OUT parameters from version 8.1
Example 5: In below example a, b, c are IN parameters and max, min are OUT parameters.
Inside the function, we get the max and minimum numbers of three IN parameters using GREATEST and LEAST built-in functions. Here we are using the OUT parameters, so no need to have a RETURN statement. The OUT parameters are useful in a function that needs to return multiple values without defining a custom type.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE OR REPLACE FUNCTION Max_Min( a NUMERIC, b NUMERIC, c NUMERIC, OUT max NUMERIC, OUT min NUMERIC) AS $$ BEGIN max := GREATEST(a,b,c); min := LEAST(a,b,c); END; $$ LANGUAGE plpgsql; |
|
1 |
Select Max_Min(4,2,9); |
|
1 2 3 |
Max_Min Record -------------- (9,2) |
Example 6: PL/pgSQL INOUT parameters
The INOUT parameter is the combination of IN and OUT parameters. It means that the caller can pass the parameter (value) to the function. The function then changes the argument and passes the value back as a part of the result.
|
1 2 3 4 5 6 |
CREATE OR REPLACE FUNCTION cal_square(INOUT num NUMERIC) AS $$ BEGIN num := num * num; END; $$ LANGUAGE plpgsql; |
|
1 |
Execute Function:
|
1 |
select cal_square(3); |
Output Of Function:
9
In this example num is a INOUT parameter, same variable we are sending as a parameter to the function and same variable we are using to calculate square.
Example 7: PL/pgSQL VARIADIC parameters
This parameter accepts a variable with one condition that all arguments have the same data type. The arguments are passed to the function as an array.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE OR REPLACE FUNCTION sum_avg( VARIADIC list NUMERIC[], OUT total NUMERIC, OUT average NUMERIC) AS $$ BEGIN SELECT INTO total SUM(list[i]) FROM generate_subscripts(list, 1) g(i); SELECT INTO average AVG(list[i]) FROM generate_subscripts(list, 1) g(i); END; $$ LANGUAGE plpgsql; |
Execute Function:
|
1 |
SELECT * FROM Sum_Avg(20,30,40); |
|
1 2 3 |
Total | Average -------+----------- 90 | 30.000 |
Conclusion:
This is all about stored functions in PostgresSQL. In PostgreSQL, both stored procedures and Stored functions( user-defined functions) are created with CREATE FUNCTION syntax but still there are few differences between these two. So in next article we will see Stored Procedures and its examples.
