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