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
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...