• Moumita Ray Systems Engineer

SUMMARY: This article reviews stored procedures in PSQL. It defines a stored procedure and describes the differences between procedures and functions, how they are created in different Postgres versions, and how to use autonomous transactions to call them.

               1. Using CREATE FUNCTION in Postres 9.6

              2. Using CREATE PROCEDURE in Postgres 11

              3. Autonomous transactions

 

A stored procedure is basically a set of precompiled SQL and procedural statements (declarations, assignments, loops, etc.) that is stored on the database server and can be invoked using the SQL interface to perform a special operation.

Until PostgreSQL version 11, both stored procedures and user-defined functions were created with the CREATE FUNCTION statement. However, beginning with PostgreSQL version 11, procedures can be created using the CREATE PROCEDURE statement. Moreover, as an added advantage, you will now be able to run transactions directly inside a procedural code. Basically, PostgreSQL version 11 allows users to perform autonomous transactions like COMMIT or ROLLBACK inside a procedural code that can be invoked using the CALL keyword.

Let's see how it works.

 

Using CREATE FUNCTION in Postgres 9.6

Here is an example of how to create a user-defined function using CREATE FUNCTION in Postgres 9.6. First we confirm the Postgres version in use:

postgres=# select version();

                                                  version                                                  

-----------------------------------------------------------------------------------------------------------

 PostgreSQL 9.6.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit

(1 row)

 

Create a table and insert data

postgres=# CREATE TABLE SAMPLE

postgres-# (

postgres(#   sno integer NOT NULL,

postgres(#   siid integer,

postgres(#   sname varchar(20),

postgres(#   sd date,

postgres(#   ed date,

postgres(#   sid integer,

postgres(#   status boolean DEFAULT false,

postgres(#   CONSTRAINT pk_snoa PRIMARY KEY (sno)

postgres(# );

CREATE TABLE

postgres=# INSERT INTO SAMPLE (sno, siid, sd, ed, sid, status)

postgres-#  VALUES (1,101,'2013-04-04','2013-04-04',2,'f' );

INSERT 0 1



postgres=# INSERT INTO SAMPLE (sno, siid, sd, ed, sid, status) VALUES (2, 103, '1993-12-24', '1995-12-02', 4, 't');

INSERT 0 1

 

Create a function

postgres=# CREATE FUNCTION sample_insert(_sno integer, _siid integer, _sd date, _ed date, _sid integer, _status boolean)

  RETURNS void AS

  $BODY$

      BEGIN

        INSERT INTO sample (sno, siid, sd, ed, sid, status)

        VALUES (_sno, _siid, _sd, _ed, _sid, _status);

      END;

  $BODY$

  LANGUAGE 'plpgsql' 

  COST 100;

CREATE FUNCTION

Execute the function

Now, we execute the function and can see that data is getting inserted:

postgres=# select * from sample_insert(3,103,'1993-12-24','1995-12-02',4,'t' );

 sample_insert 

---------------



(1 row)



postgres=# select 

postgres-# * from sample;

 sno | siid | sname |     sd     |     ed     | sid | status 

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

   1 |  101 |       | 2013-04-04 | 2013-04-04 |   2 | f

   2 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | t

   3 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | t

(3 rows)



postgres=# select * from sample_insert(4,103,'1993-12-24','1995-12-02',4,'f' );

 sample_insert 

---------------



(1 row)



postgres=# select                                                              

* from sample;

 sno | siid | sname |     sd     |     ed     | sid | status 

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

   1 |  101 |       | 2013-04-04 | 2013-04-04 |   2 | f

   2 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | t

   3 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | t

   4 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | f

(4 rows)



postgres=# 







==========================================================================================

 

Using CREATE PROCEDURE in Postgres 11

Here is an example of a procedure created using CREATE PROCEDURE in Postgres 11. First we confirm the Postgres version in use:

postgres=# select version();

                                                 version                                                 

---------------------------------------------------------------------------------------------------------

 PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

(1 row)

 

Create a table and insert data

postgres=# CREATE TABLE SAMPLE

postgres-# (

postgres(#   sno integer NOT NULL,

postgres(#   siid integer,

postgres(#   sname varchar(20),

postgres(#   sd date,

postgres(#   ed date,

postgres(#   sid integer,

postgres(#   status boolean DEFAULT false,

postgres(#   CONSTRAINT pk_snoa PRIMARY KEY (sno)

postgres(# );

CREATE TABLE



postgres=# INSERT INTO SAMPLE (sno, siid, sd, ed, sid, status)

postgres-#  VALUES (1,101,'2013-04-04','2013-04-04',2,'f' );

INSERT 0 1



postgres=# INSERT INTO SAMPLE (sno, siid, sd, ed, sid, status) VALUES (2, 103, '1993-12-24', '1995-12-02', 4, 't');

INSERT 0 1

 

Create a Procedure

We can create a procedure using the CREATE PROCEDURE syntax:

postgres=# CREATE PROCEDURE sample_insert(_sno integer, _siid integer, _sd date, _ed date, _sid integer, _status boolean)

postgres-# LANGUAGE SQL

postgres-# AS $BODY$

postgres$#     INSERT INTO SAMPLE(sno, siid, sd, ed, sid, status)

postgres$#     VALUES(_sno, _siid, _sd, _ed, _sid, _status);   

postgres$# $BODY$;

CREATE PROCEDURE

 

Call the Procedure

postgres=# CALL sample_insert (3, 103, '1993-12-24', '1995-12-02', 4, 't');

CALL

postgres=# 





postgres=# select * from sample;

 sno | siid | sname |     sd     |     ed     | sid | status 

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

   1 |  101 |       | 2013-04-04 | 2013-04-04 |   2 | f

   2 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | t

   3 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | t

(3 rows)



postgres=# CALL sample_insert (4, 103, '1993-12-24', '1995-12-02', 4, 'f');

CALL

postgres=# select * from sample;

 sno | siid | sname |     sd     |     ed     | sid | status 

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

   1 |  101 |       | 2013-04-04 | 2013-04-04 |   2 | f

   2 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | t

   3 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | t

   4 |  103 |       | 1993-12-24 | 1995-12-02 |   4 | f

(4 rows)



postgres=# 

 

Autonomous Transactions

Procedures allow you to define autonomous transactions like COMMIT or ROLLBACK within the procedure:

postgres=# CREATE OR REPLACE PROCEDURE test() 

postgres-# LANGUAGE plpgsql 

postgres-# AS $$

postgres$# DECLARE

postgres$# BEGIN

postgres$#   CREATE TABLE tnew1 (id int);

postgres$#   INSERT INTO tnew1 VALUES (1);

postgres$#   COMMIT;

postgres$#   CREATE TABLE tnew2 (id int);

postgres$#   INSERT INTO tnew2 VALUES (1);

postgres$#   ROLLBACK;

postgres$# END $$;

CREATE PROCEDURE



postgres=# call test();

CALL

postgres=# select 

postgres-# * from tnew1;

 id 

----

  1

(1 row)

 

NOTE: We can't define autonomous transactions inside a function.

 

Reference links

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