I was going to give a short talk on this subject in pgconf New York 2020, however unfortunately the conference like many other conference this year got cancelled due to COVID-19. So I decided to write a short blog about this schema object introduced in PG 11. As a Oracle database developer / DBA in my previous jobs, I had made extensive use of stored procedure and finally it landed in PostgreSQL in PG 11.  

Stored functions have been around for a while, PostgreSQL supports creating them in language of user’s choice i.e. SQL / plpgsql, C  and python etc. The support for Stored procedure was added in PostgreSQL 11, this blog is about this much awaited feature, the main differences between functions and procedures, how they are compiled and executed from different applications and some other aspects of this feature. I can’t cover all aspect of stored procedure in one blog so I have talked about some key things and will leave the rest for a talk or second part of this blog. 

Stored procedure or a Stored function is a module or a program that is stored in the database, it extends the database functionality by creating and reusing user defined programs in supported SQL/PL languages. They can be created in multiple languages (details to follow) and once compiled they become a schema object which can be executed or referenced by multiple applications.  The stored procedures/functions are very useful component of a database application as they underpin the complex application logic and database operations that needs to executed and reused multiple times by the database application. Without this feature it would become very complex to carry out database operations that need to repeated, it will be done using several complex SQL queries with round trips in a single function within the database. 

Difference : Functions vs Stored Procedure

There are some subtle differences between a function and and stored procedure : 

1. The main difference between the two is that functions don’t support transactions. This means that a transaction can’t be started in function nor a transaction can get committed or rolled back. The stored procedure is executed as a transaction, it can include more sub-transactions inside the stored procedure that can be committed or rolled back. The following example demonstrates how a transaction works inside stored procedures and what happens when a transaction is included in a function.
   

postgres=# CREATE OR REPLACE FUNCTION updStudent_fun(IN st_id INT,IN st_class VARCHAR, IN commit_rule boolean)
postgres-# RETURNS INT AS $upd_Student$ 
postgres$# BEGIN
postgres$#         -- updating student's mask
postgres$#         UPDATE student
postgres$#         SET total_marks= total_marks-10
postgres$#         WHERE std_id = st_id;
postgres$# 
postgres$#         -- promoting the class
postgres$#         UPDATE student
postgres$#         SET class = st_class
postgres$#         WHERE std_id = st_id;
postgres$# 
postgres$#         if commit_rule then
postgres$#                 commit;
postgres$#         else
postgres$#                 rollback;
postgres$#         end if;
postgres$#         Return 1;
postgres$# END;  

postgres=# call updStudent(1,'4B',false);
CALL

As shown above, the procedure is called from PSQL and the database operations done inside the procedure are committed.

postgres=# CREATE OR REPLACE FUNCTION updStudent_fun(IN st_id INT,IN st_class VARCHAR, IN commit_rule boolean)
postgres-# RETURNS INT AS $upd_Student$ 
postgres$# BEGIN
postgres$#         -- updating student's mask
postgres$#         UPDATE student
postgres$#         SET total_marks= total_marks-10
postgres$#         WHERE std_id = st_id;
postgres$# 
postgres$#         -- promoting the class
postgres$#         UPDATE student
postgres$#         SET class = st_class
postgres$#         WHERE std_id = st_id;
postgres$# 
postgres$#         if commit_rule then
postgres$#                 commit;
postgres$#         else
postgres$#                 rollback;
postgres$#         end if;
postgres$#         Return 1;
postgres$# END;    
postgres$# $upd_Student$ Language plpgsql;
CREATE FUNCTION

postgres=# select updStudent_fun(1,'4B',false);
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function updstudent_fun(integer,character varying,boolean) line 16 at ROLLBACK

As you can see above, when a transaction is used inside a function it returns a meaningful error.

2. Function need to specify a return type and return value while Stored procedures don’t have a return value. INOUT parameter are used for returning values from a stored procedure. I have given a simple example below to show how to return values from a stored procedure.

Stored Procedure Structure

The minimal syntax for creating a stored procedure is the following :

CREATE [OR REPLACE] PROCEDURE proc_name(param_list)
LANGUAGE language_name
AS 
$
   procedure_body;
$;

Similar to a function, stored procedure can be created in SQL, C, internal or PG procedural language i.e. PL/PGSQL. Stored procedure doesn’t have a return type, inout parameter can be used to return values from a stored procedure. I have show a simple example below to show a value is returned from a stored procedure.

The complete syntax for creating a stored procedure is given below, please refer to PG documentation https://www.postgresql.org/docs/11/sql-createprocedure.html for syntax and meaning/usage of each construct used for creating a stored procedure.

CREATE [ OR REPLACE ] PROCEDURE
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
{ LANGUAGE lang_name
| TRANSFORM { FOR TYPE type_name } [, ... ]
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| SET configuration_parameter { TO value | = value | FROM CURRENT }
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...

Invoking a Stored Procedure

The stored procedure can be invoked from PSQL using the CALL command as shown below :

postgres=# call updStudent(1,'4B',false);
CALL

You can also call a stored procedure from another PL program like below :

postgres=# CREATE OR REPLACE PROCEDURE call_updstudent()
postgres-# LANGUAGE plpgsql
postgres-# AS $$
postgres$# BEGIN
postgres$#         -- updating student
postgres$#         call  updStudent(1,'4B',false);
postgres$# END;
postgres$# $$;
CREATE PROCEDURE
postgres=# 
postgres=# call call_updstudent();
CALL

Stored procedure can also be exposed from external java or C etc programs using the approproate driver, the PG JDBC driver is used for executing a java program with PG.

In order to call a stored proceudre from Java, you need to use CallableStatement Interface. The prepareCall() method of Connection interface returns the instance of CallableStatement. The user would need to set the setString function for registering the IN parameter values and registerOutParameter method of the CallableStatement in interface for getting out parameter values. Lots of exmaples are available on the net for calling stored procedures from Java. I will include examples of calling stored procedures from Java, C and using stored procedures of languages other then PL/PGSQL in my subsequent blog on this subject.

Returning a value from Stored Procedure

Stored procedure don’t have a return value, INOUT parameters are used for returning a value from a stored procedure. The simple example below show how to do that. Please note OUT parameters are currently not supported with stored procedures, need to use INOUT instead.

The procedure below will return total_marks from student table as a INOUT parameter value.

CREATE OR REPLACE PROCEDURE updStudent(IN st_id INT,IN st_class VARCHAR, IN commit_rule boolean, INOUT tot_marks INT default 0)
LANGUAGE plpgsql
AS $$
BEGIN
        -- updating student's mask
        UPDATE student
        SET total_marks= total_marks-10
        WHERE std_id = st_id;

        -- promoting the class
        UPDATE student
        SET class = st_class
        WHERE std_id = st_id;

        if commit_rule then
                commit;
        else
                rollback;
        end if;

        select sum(total_marks) into tot_marks from student;
        
END;
$$;

Creating a procedure below to call the stored procedure with INOUT parameter.

CREATE OR REPLACE PROCEDURE call_updstudent()
LANGUAGE plpgsql
AS $$
Declare
   tot_marks INT;
BEGIN
        -- updating student
        call  updStudent(3,'4B',true, tot_marks);
        raise info 'total marks : %',tot_marks;
END;
$$;

Calling the wrapper procedure and printing the value of INOUT parameter returned by the stored procedure.

postgres=# call call_updstudent();
INFO:  total marks : 543
CALL
postgres=# 

Overloading Stored Procedure

Like functions, stored procedure can also share same name in a same schema with different arguments. Here is a simple example where we have created three variations of a stored procedure.

CREATE OR REPLACE FUNCTION updStudent_fun(IN st_id INT,IN st_class VARCHAR)

CREATE OR REPLACE FUNCTION updStudent_fun(IN st_id INT,IN st_class VARCHAR, IN commit_rule boolean)

CREATE OR REPLACE PROCEDURE updStudent(IN st_id INT,IN st_class VARCHAR, IN commit_rule boolean, INOUT tot_marks INT default 0)

Query pg_proc to see the overloaded stored procdure :

postgres=# select proname,pronargs from pg_proc where lower(proname)='updstudent';
  proname   | pronargs 
------------+----------
 updstudent |        2
 updstudent |        3
 updstudent |        4
(3 rows)

Conclusion

I have covered some key aspect of a interesting feature i.e. Stored Procedure in PG-11, the blog is not a endless list of things that we can do with stored procedures. I will plan to cover the remaining aspects in subsequent blogs.

Ahsan Hadi

Ahsan Hadi