# Stored procedures vs functions

A stored procedure in PostgreSQL is like a function, but you can control transactions and you can't return values. That means they are more suited to executing statements that affect and modify the database, and not for modifying data being selected by one of your queries.

Stored procedures were added in PostgreSQL 11. Many online tutorials and guides written prior to PostgreSQL 11 refer to functions as "procedures"^[1]. These tutorials may still be good, and just use the wrong name. Be careful and make sure you really know what they're talking about!

A stored procedure is similar to a function in its construction:

CREATE [OR REPLACE] PROCEDURE procedure_name(parameters) AS $$
    procedure_body
$$ LANGUAGE SQL;

You can use languages other than SQL, just like in functions^[2].

Since stored procedures don't return values, you don't call them using SELECT. Instead, we use CALL:

CALL procedure_name();

I seldom use stored procedures, but the key thing about them is you've got transaction control. That means that you can execute a group of statements in one go, and you're sure that they will all execute, or none will^[3].

You might create a stored procedure when you want to perform a series of changes to a table, all of which must happen, and which you do often.

The typical example^[4] is a bank transfer. If you're taking money from one account and putting into another, you want to make sure that both the withdrawal and the deposit happen. You should enclose these two updates in a transaction, and you can do that using a stored procedure since a banking app would likely be doing money transfers quite often.

# Creating test data with a procedure

Something you may want to create a procedure for in an application is the creation of test data (e.g. for your automated tests). This is also a good example because of a problem we'll encounter when we use LANGUAGE SQL.

Let's say we want to create a test data table and insert some data into it. We want to create a procedure so that all the queries happen, and so we can call this over and over again as necessary.

First, we create our procedure skeleton:

CREATE PROCEDURE insert_test_data() AS $$

$$ LANGUAGE SQL;

We need to add the queries we want, so we'll add these:

CREATE PROCEDURE insert_test_data() AS $$
BEGIN;
    DROP TABLE IF EXISTS test_data;
    CREATE TABLE test_data (id INTEGER, name TEXT);
    INSERT INTO test_data VALUES (1, 'Bob');
    INSERT INTO test_data VALUES (2, 'Rolf');
    COMMIT;
END;
$$ LANGUAGE SQL;

Note that the BEGIN; is necessary to start the transaction. COMMIT; later on saves the result of running the queries. END; tells PostgreSQL we're done with this transaction.

Our queries:

Drop the entire test_data table.
Create the new table.
Insert two rows.

But if you try to run that CREATE PROCEDURE, you get this:

ERROR:  syntax error at or near "DROP"
LINE 3:    DROP TABLE IF EXISTS test_data

That's because when we use SQL as our language, it verifies the queries at the moment of creating the procedure--and not only when running it.

Since the test_data table doesn't exist until the procedure is called at least once, the procedure creation fails.

If we use plpgsql instead as a language, this will work because it doesn't raise an error when deleting from a table that doesn't exist:

CREATE PROCEDURE create_test_data() AS $$
    BEGIN
    DROP TABLE IF EXISTS test_data;
    CREATE TABLE test_data (id INTEGER, name TEXT);
    INSERT INTO test_data VALUES (1, 'Bob');
    INSERT INTO test_data VALUES (2, 'Rolf');
    COMMIT;
END;
$$ LANGUAGE plpgsql;

Two changes:

BEGIN no longer uses a semicolon in plpgsql.
The language has changed to plpgsql.

The rest remains the same, but of course now the procedure cannot be migrated to another database system since it's using PostgreSQL language. When the procedure was using SQL language we could migrate it to another system, such as MySQL, more easily.

Now every time you want to run your stored procedure, which will give you fresh test data, just do:

CALL create_test_data();

# Wrapping Up

Like with functions, there's a lot more to learn about stored procedures. They can have parameters so you can pass arguments, you can have transaction savepoints, you can rollback, and much more.

Once again I'd recommend reading through the official documentation (although it is a bit lacking in the procedures department, so read the functions documentation instead). That'll tell you more about what things you can do, so that in the future you know whether something is possible or not.

← Composite types and sets in functions Connections vs. transactions →