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 …