PostgreSQL 11: Procedures and transaction control

Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL. Of course you can create functions which do not return anything but the possibility to create a procedure was not there. That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. On top of that there was another commit which enables transaction control inside procedures which can be quite interesting. Lets see how that works.

Up to PostgreSQL 10 the only choice to have something like a procedure is to create a function returning void:

postgres=# select version();
                                                          version                                                           
----------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# CREATE FUNCTION dummy_func (id int) RETURNS VOID AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   RAISE NOTICE 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# set client_min_messages = 'NOTICE';
SET
postgres=# select dummy_func(1);
NOTICE:  id is 1
 dummy_func 
------------
 
(1 row)

When you tried to do something like this it was not possible:

postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   raise notice 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
ERROR:  syntax error at or near "PROCEDURE"
LINE 1: CREATE PROCEDURE dummy_proc (id int) AS $$

But now in the PostgreSQL development version you can do it (CREATE PROCEDURE):

postgres=# select version();
                                                  version                                                   
------------------------------------------------------------------------------------------------------------
 PostgreSQL 11devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
(1 row)

postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$
postgres$# DECLARE 
postgres$# BEGIN
postgres$#   raise notice 'id is %', id;
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE PROCEDURE
postgres=# call dummy_proc(1);
NOTICE:  id is 1
CALL

Also notice that you need to use call to execute a procedure. Using select as you would do it for a function will not work:

postgres=# select dummy_proc(1);
ERROR:  dummy_proc(integer) is a procedure
LINE 1: select dummy_proc(1);
               ^
HINT:  To call a procedure, use CALL.

On top of the commit which introduced procedures there was another one (see the beginning of this post) which introduced transaction control for procedures. As the name implies this can be useful when you want to control transactions inside the procedure, e.g.:

postgres=# create table t1 ( a int primary key );
CREATE TABLE
postgres=# CREATE OR REPLACE PROCEDURE dummy_proc2 (id int) AS $$
postgres$# DECLARE 
postgres$#   l_id int := id;
postgres$# BEGIN
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   l_id := l_id + 1;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;

When you execute this it will violate the primary key with the third insert:

postgres=# call dummy_proc2(1);
NOTICE:  id is 1
NOTICE:  id is 2
NOTICE:  id is 2
ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (a)=(2) already exists.
CONTEXT:  SQL statement "insert into t1 (a) values (l_id)"
PL/pgSQL function dummy_proc2(integer) line 13 at SQL statement

… but because we can now commit (or rollback) in between we did not lose all the records:

postgres=# select * from t1;
 a 
---
 1
 2
(2 rows)

Trying to do the same with a function in PostgreSQL 10 will not work:

postgres=# create table t1 ( a int primary key );
CREATE TABLE
postgres=# CREATE FUNCTION dummy_func2 (id int) RETURNS VOID AS $$
postgres$# DECLARE 
postgres$#   l_id int := id;
postgres$# BEGIN
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   l_id := l_id + 1;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$#   commit;
postgres$#   raise notice 'id is %', l_id;
postgres$#   insert into t1 (a) values (l_id);
postgres$# END;
postgres$# $$  LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# select dummy_func2(1);
NOTICE:  id is 1
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function dummy_func2(integer) line 7 at SQL statement
postgres=# select * from t1;
 a 
---
(0 rows)

Nice features …