Many people have have been asking for this feature for years and PostgreSQL 11 will finally have it: I am of course talking about CREATE PROCEDURE. Traditionally PostgreSQL has provided all the means to write functions (which were often simply called “stored procedures”). However, in a function you cannot really run transactions - all you can do is to use exceptions, which are basically savepoints. Inside a function, you cannot just commit a transaction or open a new one. CREATE PROCEDURE will change all that and provide you with the means to run transactions in procedural code.
CREATE PROCEDURE will allow you to write procedures just like in most other modern databases. The syntax is quite simple and definitely not hard to use:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
db11=# h CREATE PROCEDURE Command: CREATE PROCEDURE Description: define a new procedure Syntax: CREATE [ OR REPLACE ] PROCEDURE name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = } default_expr ] [, ...] ] ) { LANGUAGE lang_name | TRANSFORM { FOR TYPE type_name } [, ... ] | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER | SET configuration_parameter { TO value | = value | FROM CURRENT } | AS 'definition' | AS 'obj_file', 'link_symbol' } … |
As you can see there are a couple of similarities to CREATE FUNCTION so things should be really easy for most end users.
The next example shows a simple procedure:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
db11=# CREATE PROCEDURE test_proc() LANGUAGE plpgsql AS $$ BEGIN CREATE TABLE a (aid int); CREATE TABLE b (bid int); COMMIT; CREATE TABLE c (cid int); ROLLBACK; END; $$; CREATE PROCEDURE |
The first thing to notice here is that there is a COMMIT inside the procedure. In classical PostgreSQL functions this is not possible for a simple reason. Consider the following code:
|
1 |
SELECT func(id) FROM large_table; |
What would happen if some function call simply commits? Total chaos would be the consequence. Therefore, real transactions are only possible inside a “procedure”, which is never called the way a function is executed. Also: Note that there is more than just one transaction going on inside our procedure. Because of this, a procedure is more of a “batch job”.
The following example shows, how to call the procedure I have just implemented:
|
1 2 |
db11=# CALL test_proc(); CALL |
The first two tables where committed - the third table has not been created because of the rollback inside the procedure.
|
1 2 3 4 5 6 7 8 |
db11=# d List of relations Schema | Name | Type | Owner --------+------+-------+------- public | a | table | hs public | b | table | hs (2 rows) |
To me CREATE PROCEDURE is definitely one of the most desirable features of PostgreSQL 11.0. The upcoming release will be great and many people will surely welcome CREATE PROCEDURE the way I do.
+43 (0) 2622 93022-0
office@cybertec.at
Can somebody pls tell how to call a stored procedure from NetCobol. we are able to make call to Function but not stored procedure.
can somebody please tell me how to call one procedure within another stored procedure in PostgreSQL 11.1....
Just use the CALL SQL statement.
good ^^
This is great, and i'm glad Postgres is finally getting actual procedures. Functions have served me very well, but there are a few places I know procedures would have been able to better serve the need. Will be fun to play around with soon.