PL/pgSQL function dummy_proc2(integer) line 13 at SQL statement When you execute this it will violate the primary key with the third insert: postgres=# call dummy_proc2(1) ĮRROR: duplicate key value violates unique constraint "t1_pkey"ĬONTEXT: SQL statement "insert into t1 (a) values (l_id)" Postgres$# insert into t1 (a) values (l_id) Postgres=# CREATE OR REPLACE PROCEDURE dummy_proc2 (id int) AS $$ 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 ) On top of the commit which introduced procedures there was another one (see the beginning of this post) which introduced transaction control for procedures. Using select as you would do it for a function will not work: postgres=# select dummy_proc(1) ĮRROR: dummy_proc(integer) is a procedure Postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$Īlso notice that you need to use call to execute a procedure. LINE 1: CREATE PROCEDURE dummy_proc (id int) AS $$īut now in the PostgreSQL development version you can do it ( CREATE PROCEDURE): postgres=# select version() When you tried to do something like this it was not possible: postgres=# CREATE PROCEDURE dummy_proc (id int) AS $$ĮRROR: syntax error at or near "PROCEDURE" Postgres=# set client_min_messages = 'NOTICE' Postgres=# CREATE FUNCTION dummy_func (id int) RETURNS VOID AS $$ Up to PostgreSQL 10 the only choice to have something like a procedure is to create a function returning void: postgres=# select version() On top of that there was another commit which enables transaction control inside procedures which can be quite interesting.
That will probably change in PostgreSQL 11 when nothing happens which will lead to the removal of that commit. Of course you can create functions which do not return anything but the possibility to create a procedure was not there.
Up to PostgreSQL 10 it was not possible to create procedures in PostgreSQL.
0 kommentar(er)
