Using Stored Procedures to Create Custom Workflows in PostgreSQL

Aug 12, 2020 • 4 Minute Read

Introduction

PostgreSQL, by way of user-defined functions, gives the ability to write custom functionality that augments and extends existing, built-in workflows. Functions are a simple means of specifying some inputs and producing the desired output. But what if you need to create a custom workflow wrapped within a transaction that is able to be rolled back?

Enter stored procedures.

Simply defined, stored procedures are functions that are given the added capability of transactions. This is extremely powerful as it allows you to define pre-compiled workflows that can be safely committed and rolled back. Not to mention, stored procedures are fast because they consist of pre-compiled SQL that the database engine can immediately execute.

Let's dive in!

Note: Stored procedures were not available in PostgreSQL until version 11.

Creating a Basic Stored Procedure

First, let's go over some stored procedure basics. Stored procedures are, at the core, simply user-defined functions. One key difference between stored procedures and user-defined functions is that stored procedures do not have a return value. You can still use the RETURN keyword to exit the procedure early, but you cannot actually return a value using this statement.

Stored procedures consume parameters just like functions. These parameters can be specified as either IN or INOUT parameters. To capture output values from the result of calling a stored procedure, you must define your stored procedure to consume an INOUT parameter.

Let's create our first stored procedure.

      CREATE OR REPLACE PROCEDURE insert_funds(IN amount INT, IN account_id INT)
    BEGIN
        -- ...
    END;
$ LANGUAGE plpgsql;
    

The SQL above constitutes the shell of a stored procedure that will eventually insert funds into a given account. By putting this update into a stored procedure, you can wrap this very important workflow in a transaction. To complete this stored procedure, and to create the transaction, just fill out the body of the procedure. You will accomplish this in the next section!

Committing and Rolling Back Transactions

It's now time to fill in the logic for your add_funds stored procedure. This procedure will consist of both an UPDATE to the account table to update the total for an account and an INSERT into the transaction_history table.

      CREATE OR REPLACE PROCEDURE add_funds(IN in_amount INT, IN in_account_id INT)
    BEGIN
        UPDATE account
        SET total = total + in_amount
        WHERE account_id = in_account_id;

        INSERT INTO transaction_history(is_credit, amount, account_id)
        VALUES(TRUE, in_amount, in_account_id);

        COMMIT;

        EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
    END;
$ LANGUAGE plpgsql;
    

There you have it! In the above code, the update and insertion statements are performed first. Then, use the COMMIT SQL keyword to wrap the insertion and update statements within a transaction. Finally, on any error, ensure that you rollback the transaction! This is a key part of the procedure and ensures that if an update to the account table fails, you don't keep track of the transaction in the transaction_history table.

All that's left to do is to execute the procedure. To add funds to an account, execute your procedure by using the CALL keyword, like this:

      CALL add_funds(50000, 118);

Conclusion

In this guide, you have learned how to create a custom workflow using a stored procedure. You have seen how stored procedures provide massive benefits, such as:

Increased execution speed
Safe execution via transactions
Modularity

You can now be confident in creating your own stored procedures that can safely and quickly execute your own workflows. For more information regarding PostgreSQL stored procedures, check out the documentation.