How to develop a user-defined function in PostgreSQL stored procedures

Linux x86-64 (RHEL 8)

Tushar Ahuja Sr. QA Manager

SUMMARY: This article covers how to create user-defined functions using PL/pgSQL procedural language in PostgreSQL. It introduces user-defined functions and gives examples of their use in different scenarios:

1. PL/pgSQL

2. User-defined functions and procedures

3. CREATE FUNCTION statement syntax

4. Examples of user-defined functions

 

In this post, We are going to see how to develop user defined function in PostgreSQL stored procedures.

PL/pgSQL 

In PostgreSQL, there are various procedural languages such as PL/pgSQL, TCL, Python, and Perl that are referred to as stored procedures. PL/pgSQL is the procedural language that comes by default with PostgreSQL. 

We will use the PL/pgSQL procedure language here. It allows us to perform more complex tasks, such as easy computation, as compared to SQL, and also makes use of loops, functions, procedures, and much more. 

 

User-defined functions and procedures 

User-defined functions and procedures are simply blocks of SQL statements that perform some task. Once the function is compiled the code can be reused over and over again.

Places in a SQL expression where user-defined functions can be used:

  • SELECT statement list:
select col1, col2, function_name() from table_name;
  • WHERE clause condition: 
 select * from table_name where col_name=function_name(); 
  • VALUES clause of an INSERT statement:
Insert into table_name values (col1,col2,function_name());
  • SET clause of an UPDATE query: 
Update table_name set n=function_name();

 

A drawback of user-defined functions is that the user cannot run a transaction in a function—i.e., the user cannot perform COMMIT or ROLLBACK.

 

Example

Create a function using a COMMIT statement in the body:

postgres=# create or replace function func2(n int) 

returns void 

as 

$$  

begin

commit; 

end;

 $$ language 'plpgsql';

CREATE FUNCTION

 

Execute the function using a SELECT statement. This should should throw an error: 

postgres=# select func2(9);

ERROR:  invalid transaction termination

CONTEXT:  PL/pgSQL function func2(integer) line 1 at COMMIT

postgres=# 

 

User-defined functions are similar to procedures. The difference is that functions always returns a value, whereas procedures do not return a value; in procedures we can also perform COMMIT and ROLLBACK commands and new transactions. 

 

Example

Create a procedure:

postgres=# create procedure pro() 

                   language plpgsql

        As

       $$

       begin

                  create table my_table(n int);

                  commit;

                  insert into my_table values (9);

                  commit;

                  delete from my_table;

                  rollback;

                  End; 

                  $$;

CREATE PROCEDURE

 

Execute the procedure using a CALL statement:  

postgres=# call pro();

CALL

postgres=# select * from my_table;

 n 

---

 9

(1 row)

 

Following the steps in the procedure, the table has been created and one record inserted, but the DELETE transaction has been rolled back due to the ROLLBACK command. 

PostgreSQL started supporting procedures with version 11. The CALL command is used to invoke a procedure. 

Please refer to the PostgreSQL documentation for more information on procedures: 

https://www.postgresql.org/docs/11/sql-createprocedure.html.

Please refer to the PostgreSQL documentation for more information on functions:

 https://www.postgresql.org/docs/11/sql-createfunction.html.

 

CREATE FUNCTION statement syntax

These are the parts of the CREATE FUNCTION statement:

1. CREATE FUNCTION fun_name(p1 type, p2 type)

2. RETURNS type AS $$

3. BEGIN

4. <<piece of code>>

5. END;

6. $$ LANGUAGE language_name;

 

A brief explanation for each step: 

1. ’fun_name’ is the user-defined function name, and p1 and p2 are the parameters.

2. Return type of the function—e.g., int, text. 

3. Begin signals that the block has started.

4. User code—e.g., DML statements. 

5. End keyword signals the end of the block.

6. Specify the procedural language—in our case, plpgsql.

Please refer to the PostgreSQL documentation for more information on CREATE FUNCTION: https://www.postgresql.org/docs/11/sql-createfunction.html.

 

Examples of user-defined functions 

Create a table:

Create table test(n int ,n1 date);

 

Insert a few records:

insert into test values (1,'2019-11-21’);

insert into test values (2,'2019-11-22');

insert into test values (3,'2019-11-23');

insert into test values (4,'2019-11-24');

insert into test values (5,'2019-11-25');

 

Check the data:

postgres=# select * from test;

 n |     n1     

---+------------

 1 | 2019-11-21

 2 | 2019-11-22

 3 | 2019-11-23

 4 | 2019-11-24

 5 | 2019-11-25

(5 rows)

 

In PostgreSQL, we have three argument modes: IN, OUT, and INOUT. If we don’t specify argument mode, then it will take IN by default. Argument modes OUT and INOUT cannot be used with the RETURNS TABLE notation.

IN -send values to function,OUT - get values from function and INOUT - does both.  

Example 1: Create a user-defined function using default IN argument mode 

In this example, if no argument mode is provided while specifying the parameter name and type, then it will take IN by default.

Pass a value to a function and then insert that value into table “test”: 

postgres=# Create or replace function fun1(n int) returns int 

as

$$

Begin

Insert into test values (n,'2019-11-26');

Return 1;

End;

$$

Language 'plpgsql';

CREATE FUNCTION

postgres=# 

 

Call the function from a SELECT query: 

postgres=# select fun1(10);

 fun1 

------

    1

(1 row)

 

Check whether data has been inserted into the table: 

postgres=# select * from test;

 n  |     n1     

----+------------

  1 | 2019-11-21

  2 | 2019-11-22

  3 | 2019-11-23

  4 | 2019-11-24

  5 | 2019-11-25

 10 | 2019-11-26       <--row has been inserted 

(6 rows)

 

Example 2: Create a user-defined function using OUT argument mode.

In this example the argument mode is OUT. 

Take a value from a function and then insert that value into table “test”:

postgres=# Create or replace function fun2(n1 out int) 

returns int 

as

$$

begin 

n1:=11;  

end;

$$

language 'plpgsql';

CREATE FUNCTION



postgres=# insert into test values (fun2(),'2019-11-27');

INSERT 0 1

postgres=# select * from test;

 n  |     n1     

----+------------

  1 | 2019-11-21

  2 | 2019-11-22

  3 | 2019-11-23

  4 | 2019-11-24

  5 | 2019-11-25

 10 | 2019-11-26

 11 | 2019-11-27     <--row has been inserted    

(7 rows)

 

Example 3: Create a user-defined function using INOUT argument mode

In this example, argument mode is INOUT. As mentioned earlier, the INOUT argument mode will work for both IN and OUT—it will send the value to a function and also get the value from a function.

Send and take a value from a function and then inserting that value into table “test”:

postgres=# Create or replace function fun3(n inout int) 

returns int 

as

$$

begin 

n:=n+1;  

end;

$$

language 'plpgsql';

CREATE FUNCTION



postgres=# insert into test values (fun3(11),'2019-11-27');

INSERT 0 1



postgres=# select * from test;

 n  |     n1     

----+------------

  1 | 2019-11-21

  2 | 2019-11-22

  3 | 2019-11-23

  4 | 2019-11-24

  5 | 2019-11-25

 10 | 2019-11-26

 11 | 2019-11-27

 12 | 2019-11-27       <--row has been inserted 

(8 rows)



postgres=#

 

Example 4: Calling a procedure from a user-defined function 

Create a procedure, where we are performing a DELETE operation on table “test”:

postgres=#create or replace procedure pro1()  

as 

$$

begin 

delete from test;  

end; 

$$ language 'plpgsql' ; 

CREATE PROCEDURE

 

Create function and invoke the procedure in it:

postgres=# Create or replace function fun1(n int) returns int 

As

$$

Begin

call pro1(); 

Return 1;                    

End;                  

$$

Language 'plpgsql';

CREATE FUNCTION

 

Execute the function using a SELECT statement:

postgres=# select fun1(1);

 fun1 

------

    1

(1 row)

 

Verify the data has been deleted and removed: 

postgres=# select * from test;

 n | n1 

---+----

(0 rows)



postgres=# 

 

Hope it helps!

 

Tushar AhujaSr. QA Manager