Stored procedures are a core concept which can be found in most relational database systems. They have proven to be useful in many areas and have been widely adopted by developers and DBA's alike.
Table of Contents
In PostgreSQL stored procedures have been around for a number of years. The syntax of this important command is defined as follows:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
blog=# 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' } ... URL: https://www.postgresql.org/docs/15/sql-createprocedure.html |
Essentially, the syntax is pretty close to CREATE FUNCTION. However, there are of course differences which we will discuss a little later.
Before we dive into the differences between functions and stored procedures it makes sense to take a look at a basic example:
|
1 2 3 4 5 6 7 8 9 |
CREATE TABLE IF NOT EXISTS t_demo (id int); CREATE OR REPLACE PROCEDURE sample_1(x int) LANGUAGE SQL AS $$ INSERT INTO t_demo VALUES (x); $$; CALL sample_1(1000); |
In the above example you first create a table and then implement a procedure. What's noteworthy here is that the procedure is called using the CALL command instead of embedding the function call into a normal SQL - but more on that later.
Instead of SQL code, you can also make use of PL/pgSQL code just like you would in a function. The following example shows how this can be done:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
CREATE OR REPLACE PROCEDURE sample_2() LANGUAGE plpgsql AS $$ DECLARE v_sum int8; BEGIN INSERT INTO t_demo VALUES (1); INSERT INTO t_demo VALUES (2); COMMIT; INSERT INTO t_demo VALUES (3); INSERT INTO t_demo VALUES (4); SELECT sum(id) FROM t_demo WHERE id < 5 INTO v_sum; RAISE NOTICE 'debug info: %', v_sum; END; $$; |
In general, the code is again pretty similar to a function. However, there is a major difference: What you see here is a COMMIT happening in the middle of the code.
The most important factor is: In a normal “server side function” the entire code is always part of the parent transaction. Imagine the following piece of code:
|
1 |
SELECT func(x) FROM tab; |
The func(x) part is not allowed to control transactions because the function has to be part of the statement. We cannot just commit after processing the first, say, 1 million rows and proceed. That is why a procedure is never part of a SELECT statement but has to be called differently:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
demo=# CALL sample_2(); NOTICE: debug info: 10 CALL demo=# SELECT * FROM t_demo; id ------ 1000 1 2 3 4 (5 rows) |
In this case, the first couple of rows have been written in a different transaction than the rest of the lines. In order to prove that, we can add the xmin column. xmin is a hidden field containing the transaction ID which wrote the row in the first place:
|
1 2 3 4 5 6 7 8 9 |
demo=# SELECT xmin, * FROM t_demo; xmin | id --------+------ 3060775 | 1000 3060780 | 1 3060780 | 2 3060781 | 3 3060781 | 4 (5 rows) |
When looking at the code you can see that after the COMMIT, there is no explicit BEGIN / START TRANSACTION. This is relevant because once a transaction commits inside a procedure, a new transaction is automatically started. In this respect, the code inside a procedure is similar to the “COMMIT AND CHAIN” command you may already be familiar with.
The fact that a procedure can span multiple transactions will add some limitations.
Consider the following example:
|
1 2 3 4 5 6 7 8 9 |
demo=# BEGIN; BEGIN demo=*# CALL sample_2(); ERROR: invalid transaction termination CONTEXT: PL/pgSQL function sample_2() line 7 at COMMIT demo=!# ROLLBACK; ROLLBACK |
Remember: Our procedure commits in the middle so therefore this is not allowed. However, in case the stored procedure does NOT handle transactions explicitly the situation is quite different:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE OR REPLACE PROCEDURE sample_3() LANGUAGE plpgsql AS $$ DECLARE v_sum int8; BEGIN INSERT INTO t_demo VALUES (1); INSERT INTO t_demo VALUES (2); INSERT INTO t_demo VALUES (3); INSERT INTO t_demo VALUES (4); END; $$; |
In this case, the situation is quite different and we can run the procedure inside an explicit transaction block:
|
1 2 3 4 5 6 7 8 |
demo=# BEGIN; BEGIN demo=*# CALL sample_3(); CALL demo=*# COMMIT; COMMIT |
However, there is more: If you write stored procedure code, you might want to use exception blocks — but this comes with limitations. A transaction cannot be ended inside a block with exception handlers, which makes sense if you think about it. Keep in mind that an exception block in a server side function is an internal subtransaction, which of course comes with all kinds of implications.
Once in a while, it is necessary to modify a stored procedure. The way to do that is to run the ALTER PROCEDURE command. The syntax of this instruction can be found in the next listing:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
blog=# h ALTER PROCEDURE Command: ALTER PROCEDURE Description: change the definition of a procedure Syntax: ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] action [ ... ] [ RESTRICT ] ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] RENAME TO new_name ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] OWNER TO { new_owner | CURRENT_USER | SESSION_USER } ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] SET SCHEMA new_schema ALTER PROCEDURE name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] DEPENDS ON EXTENSION extension_name where action is one of: [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER SET configuration_parameter { TO | = } { value | DEFAULT } SET configuration_parameter FROM CURRENT RESET configuration_parameter RESET ALL URL: https://www.postgresql.org/docs/15/sql-alterprocedure.html |
The question is how to use this command. Let's take a look at an example:
|
1 2 3 |
demo=# ALTER PROCEDURE sample_2() SET timezone TO 'UTC'; ALTER PROCEDURE |
In this case, the procedure will use UTC as the timezone inside the stored procedure code.
Often people ask when to use stored procedures at all. Many architects consider server-side code to be “evil”. However, is that really the case? Suppose you want to process many billions of rows. Transferring the entire data set to the client and processing it there just to send the data back to the database is usually a bad idea.
In my judgment, store procedures should be used in case:
The discussion will continue and it's important to understand both sides of the argument.
If you want to dive deeper into PostgreSQL, check out this related post by Laurenz Albe: Subtransactions and Performance in PostgreSQL
In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.
+43 (0) 2622 93022-0
office@cybertec.at