Posted on by Hans-Jürgen Schönig

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.

Stored procedures basics

In PostgreSQL stored procedures have been around for a number of years. The syntax of this important command is defined as follows:


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.

Running stored procedures

Before we dive into the differences between functions and stored procedures it makes sense to take a look at a basic example:

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:


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.

Server side function

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:


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:

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:


demo=# SELECT xmin, * FROM t_demo;
xmin    | id
--------+------
3060775 | 1000
3060780 | 1
3060780 | 2
3060781 | 3
3060781 | 4
(5 rows)

CREATE PROCEDURE and transactions

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:


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:


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:

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.

Modifying stored procedures in PostgreSQL

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:


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:

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.

When to use stored procedures

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:

  • A lot of data is processed (keep the calculation close to the data)
  • In case transactional integrity is needed
  • When calculations are data-centric

The discussion will continue and it’s important to understand both sides of the argument.

Finally …

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.

Hans-Jürgen Schönig

Hans-Jürgen Schönig has experience with PostgreSQL since the 90s. He is CEO and technical lead of CYBERTEC, which is one of the market leaders in this field and has served countless customers around the globe since the year 2000.