PostgreSQL 11 : Procedures are coming

Reading about new features about future version PostgreSQL 11. I see that procedures will be implemented. Why is it so important? Until now only functions are available in PostgtreSQL. Of course a function is a procedure that returns a value, we can say, yes it’s true but you cannot manage transactions in a function. To better understand let’s do a quick demonstration.
Note that I am using the snapshot developer version of PostgreSQL 11 .

[[email protected]_essentials_p1 bin]$ ./psql
Null display is "NULL".
psql (11devel)
Type "help" for help.

([email protected][local]:5432) [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-4), 64-bit
(1 row)

([email protected][local]:5432) [postgres] >

For the demonstration I have a table emp

([email protected][local]:5432) [test] > table emp;
 id | name
----+------
  1 | toto
  2 | Edge
(2 rows)

([email protected][local]:5432) [test] >

And let’s say I want to insert data in my table using following function

CREATE or replace FUNCTION fun_insert_emp(id_emp int,  emp_name varchar(20))
 RETURNS  void AS $$
BEGIN
 insert into emp (id,name) values (id_emp,emp_name);
commit;
END;
$$
LANGUAGE PLPGSQL;

We can describe the function like this

([email protected][local]:5432) [test] >  sf  fun_insert_emp                           
CREATE OR REPLACE FUNCTION public.fun_insert_emp(id_emp integer, emp_name character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
 insert into emp (id,name) values (id_emp,emp_name);
commit;
END;
$function$
([email protected][local]:5432) [test] >

To call a function we use a select like any built-in function. So let’s try to insert a new employee with the function

([email protected][local]:5432) [test] > select fun_insert_emp(3,'New Emp');
ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function fun_insert_emp(integer,character varying) line 4 at COMMIT
([email protected][local]:5432) [test] >

Seems that the word COMMIT is causing trouble. OK let’s recreate the function without the COMMIT

([email protected][local]:5432) [test] >  sf  fun_insert_emp                           
CREATE OR REPLACE FUNCTION public.fun_insert_emp(id_emp integer, emp_name character varying)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
BEGIN
 insert into emp (id,name) values (id_emp,emp_name);
END;
$function$
([email protected][local]:5432) [test] >

And let’s call again the function. We can see that the row was inserted

([email protected][local]:5432) [test] > select fun_insert_emp(3,'New Emp');             
fun_insert_emp
----------------

(1 row)

([email protected][local]:5432) [test] > table emp;
 id |  name
----+---------
  1 | toto
  2 | Edge
  3 | New Emp
(3 rows)

([email protected][local]:5432) [test] >

But the main problem we saw is that a function in PostgreSQL does not support any transaction instruction (BEGIN…COMMIT, BEGIN…ROLLBACK). This means that in a function I cannot use instructions that allow to rollback or commit based on some conditions.
But PostgreSQL 11 will support procedure. Let’s do again the demonstration with a procedure.
Let’s first create the procedure

([email protected][local]:5432) [test] > create or replace procedure proc_insert_emp (id_emp int,  emp_name varchar(20))
test-# as $$
test$# Begin
test$# insert into emp (id,name) values (id_emp,emp_name);
test$# commit;
test$# end ;
test$# $$
test-# LANGUAGE PLPGSQL;
CREATE PROCEDURE
([email protected][local]:5432) [test] >

And let’s insert a new row in table emp using proc_insert_emp

([email protected][local]:5432) [test] > call  proc_insert_emp(4,'Brice');
CALL

([email protected][local]:5432) [test] > table emp;
 id |  name
----+---------
  1 | toto
  2 | Edge
  3 | New Emp
  4 | Brice
(4 rows)

([email protected][local]:5432) [test] >

We can see that the row was inserted. But the main difference is the support of autonomous transaction and this will be definitively change life for developers.