By Mouhamadou Diaw
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 .
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
[postgres@pg_essentials_p1 bin]$ ./psql Null display is "NULL" . psql (11devel) Type "help" for help. (postgres@[ 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) (postgres@[ local ]:5432) [postgres] > |
For the demonstration I have a table emp
1
2
3
4
5
6
7
8
|
(postgres@[ local ]:5432) [test] > table emp; id | name ----+------ 1 | toto 2 | Edge (2 rows ) (postgres@[ local ]:5432) [test] > |
And let’s say I want to insert data in my table using following function
1
2
3
4
5
6
7
8
|
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
1
2
3
4
5
6
7
8
9
10
11
|
(postgres@[ 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 $ (postgres@[ 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
1
2
3
4
|
(postgres@[ 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 (postgres@[ local ]:5432) [test] > |
Seems that the word COMMIT is causing trouble. OK let’s recreate the function without the COMMIT
1
2
3
4
5
6
7
8
9
10
|
(postgres@[ 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 $ (postgres@[ local ]:5432) [test] > |
And let’s call again the function. We can see that the row was inserted
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
(postgres@[ local ]:5432) [test] > select fun_insert_emp(3, 'New Emp' ); fun_insert_emp ---------------- (1 row) (postgres@[ local ]:5432) [test] > table emp; id | name ----+--------- 1 | toto 2 | Edge 3 | New Emp (3 rows ) (postgres@[ 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
1
2
3
4
5
6
7
8
9
10
|
(postgres@[ 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 (postgres@[ local ]:5432) [test] > |
And let’s insert a new row in table emp using proc_insert_emp
1
2
3
4
5
6
7
8
9
10
11
12
13
|
(postgres@[ local ]:5432) [test] > call proc_insert_emp(4, 'Brice' ); CALL (postgres@[ local ]:5432) [test] > table emp; id | name ----+--------- 1 | toto 2 | Edge 3 | New Emp 4 | Brice (4 rows ) (postgres@[ 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.