Ever written a bunch of SQL once and hoped you’d never have to see it again? Stored procedures in Postgres are your friend—they’re reusable functions that you can call as part of a query and store in your actual database. This post will walk through how to build and use them, performance implications, and what makes a stored procedure vs. a function.
Functions have existed in Postgres for quite some time. Much like in any programming language, Postgres functions allow you to define reusable bits of code that take inputs and produce outputs. In Postgres specifically, functions must produce an output. If you’ve got a multistep query that involves multiple tables, or just grouped business logic that you want to execute as part of a single transaction, functions make a lot of sense for storing that information in the database.
In Postgres 11, the concept of the dedicated stored procedure was introduced formally. It’s a special type of function that:
- Doesn’t have an explicit return value (i.e. returns VOID by default)
- Can commit and abort transactions
- Is invoked via the CALL keyword, instead of existing inside a SQL command like SELECT
Stored procedures are a good fit for tasks that do something, as opposed to transforming input data or some kind of smaller function you’d want to include nested inside a normal SQL statement. While Postgres functions can be called inside of a SELECT, a WHERE, etc., stored procedures are called via CALL and cannot be nested inside other statements.
An example, you ask? Imagine you’re building an e-commerce application, and you need to build some database logic for handling new subscriptions. After the user clicks the “subscribe” button, the data workflow looks something like this:
- Insert credit card information into credit cards table
- Insert new row into the subscriptions table
- Update the user’s subscription status in the users table
If you were to build this as 3 separate queries, you’d write something like:
1# Insert credit card information into credit cards table
2
3INSERT INTO credit_cards(card_number,user_id,exp_date,sec_code)
4VALUES (4242424242424242, ‘b273r3hfoi98272’, 0724, 321)
5
6# Insert subscription into subscriptions table
7
8INSERT INTO subscriptions(subscription_id,subscription_type,user_id)
9VALUES (2672111,monthly,’b273r3hfoi98272’)
10
11# Update user subscription status in users table
12
13UPDATE users
14SET
15 subscription_status = TRUE
16 subscription_id = 2672111
17WHERE user_id = ‘b273r3hfoi98272’
18And that would be just fine, pending your ability to handle running these together in your application logic (more on this later). But you could also put these interdependent queries together into a stored procedure:
1CREATE PROCEDURE handle_subscription(user_id text, subscription_id integer…)
2
3LANGUAGE SQL
4
5AS $$
6
7# Insert credit card information into credit cards table
8…
9# Insert subscription into subscriptions table
10…
11# Update user subscription status in users table
12
13$$;
14And then call it with:
1CALL handle_subscription(‘b273r3hfoi98272’, 2672111, …);
2Note that this is a good use case for stored procedures over regular functions because your task here has no return value – you just need your database to do a few things.
The create procedure syntax is simple enough: at a minimum you need to define a name, arguments and data types, and a string defining the procedure. If the body is in SQL then Postgres will assume the language type is SQL, otherwise you specify the language you want to use with the `LANGUAGE` keyword. You can also replace an existing procedure of the same name with `CREATE OR REPLACE`.
We’ve used SQL as the language for the stored procedure here, but you have several other options. Out of the box, Postgres lets you use SQL, pgSQL, or C. You can also work with extensions to use Python, Perl, and others. Here’s how we’d update an existing stored procedure for assigning user permissions to a new user, as well as adding them to our internal users table:
1CREATE OR REPLACE PROCEDURE initiation(user_email text, user_id text)
2LANGUAGE plpgsql
3BEGIN ATOMIC
4 GRANT CONNECT ON DATABASE subscriptions TO user_id;
5 INSERT INTO internal_users VALUES(user_id, user_email)
6END;
7We’ve used the `BEGIN ATOMIC` and `END` syntax here, which is functionally equivalent to the earlier `AS $$` – you can use whichever you find more convenient. The SQL we’ve executed here could have been written in regular SQL, but there’s at least a shot it runs faster in pgSQL. Note that to create and use stored procedures, your database user is going to need a special set of permissions. To create a stored procedure, you’ll need USAGE privileges on the procedure language and argument types. To call said procedure, your user needs to be granted call permissions on it.
Outside of replacing the entire procedure, Postgres has `ALTER` functionality for changing your procedures:
- Name
- Owner
- Schema
- Action (basically, any params you configured it with)
You need to be an `OWNER` of the procedure to make any of these changes. A simple name change might look something like:
1ALTER PROCEDURE initiation(text, text) RENAME TO initiate_user;
2You can move a stored procedure to a different schema with:
1ALTER PROCEDURE initiate_user(text, text) SET SCHEMA internal_housekeeping;Speaking of schemas, stored procedures default to being stored in whichever schema is currently selected when they’re created. In other words, they’re stored on a schema basis, and you cannot use cross-schema procedures (this is a technical term I made up). You can specify which schema you want to create your procedure in by prefacing your procedure name with a particular schema. If you want to change it after the fact though, your user will need `CREATE` privileges in the target schema.
Generally, databases should be for storing data and code should be for manipulating it. The obvious question when looking at stored procedures is why would you use these instead of putting multi-query (business?) logic in your application code? Obviously not just because some senior-staff-engineer-who-was-born-querying-postgres at your company told you to – there are actually a few use cases where it makes sense (which is why the feature exists):
- You want to share logic between several servers / microservices and you don’t want to replicate the logic in several languages / services
- The stored procedure logic all relates to a single transaction that shouldn’t be committed until all of the logic is executed
- You have a table that’s entirely dependent on another table’s changes or data
A useful framework to apply: is the logic required for data consistency or integrity? If so, it makes sense to store in the database. If not, it should be in your server side code.
Like any peripheral feature in a database (see: indexes), there are performance implications for stored procedures in Postgres. In general, if you’re executing SQL in your stored procedures, they should run pretty much as quickly as raw SQL.
However – if your procedure is defined in anything other than SQL, it’s basically a black box to the query planner. If you have a particularly strong reason to use another language (Python, C, etc.) do so, but keep in mind that you’re going to take a performance hit.
If you’re writing your stored procedure in pgSQL, functions execute those commands like prepared statements. Prepared statements help you optimize the performance of your queries, but work specifically well when you plan on running a large number of relatively similar queries in a single DB session by pre-analyzing your queries before executing them. If that’s not your use case, using prepared statements can actually slow down your query’s execution, which in turn would apply to stored procedures as well.
On the whole though, both of these issues are avoidable and arguably pretty niche. For most use cases, you won’t suffer much perf-wise from using a stored procedure vs. application layer logic.
In this post, we learned the basics of stored procedures in Postgres, discussed when you might want to use them, and considered the performance implications of stored procedures versus other query types. Stored procedures are a great option when you have complex query logic that should be shared among multiple consumers of a database, like micro-services built in different programming languages. For more information on stored procedures in Postgres, you can reference the official documentation here. To learn more about using Postgres to build internal applications with Retool, check out the Postgres connector docs here!
Reader
