Stored procedures in PostgreSQL: getting started

02.2023 / Category: / Tags: | |

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:

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:

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:

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:

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:

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:

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:

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:

In this case, the situation is quite different and we can run the procedure inside an explicit transaction block:

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:

The question is how to use this command. Let's take a look at an example:

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.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram