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.

Post Views: 1,389
Thumbnail [60x60]
by
Oracle Team