SQL for QA: Tutorial#13 – Stored Functions

Supriya

5 years ago

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 donxt 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.

CREATE FUNCTION xFunction_Namex(Parameter_1 datatype,Parameter_2 datatype)
RETURNS xreturn datatypex 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

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:

SELECT Add_1(20,20);

Output:

Add_1
-----
40

Â Example 2: Function to display employee count from given table.

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:

select TotalRecords1();

Output Of Function:

TotalRecords1
--------------
  2

Example 3: function to find out factorial of a number

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:

SELECT Cal_Fact(5);

Output Of Function:

Cal_Fact
---------
 120

Example 4: Function which returns a table

Consider a following employee table

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 x= sal)
LOOP
ename := upper(var_r.e_name) ;
sala := var_r.e_sal;
RETURN NEXT;
END LOOP;
END; $$

LANGUAGE 'plpgsql';

Execute Function:

SELECT get_emp('%ta', 10000);

Output OfÂ Function:

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.Â

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;

Select Max_Min(4,2,9);

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.

CREATE OR REPLACE FUNCTION cal_square(INOUT num NUMERIC)
AS $$
BEGIN
Â Â  num := num * num;
END; $$
LANGUAGE plpgsql;

Execute Function:

select cal_square(3);

Output Of Function:

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.

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:

SELECT * FROM Sum_Avg(20,30,40);

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.