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.

  • 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

Execute Function:


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

Execute Function:

Output Of Function:

Example 3: function to find out factorial of a number

Execute Function:

Output Of Function:

Example 4: Function which returns a table

Consider a following employee table

Employee Table

Execute Function:

Output Of Function:

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. 

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.

Execute Function:

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.

Execute Function:


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.

Add a Comment
