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.