Using Stored Procedures to Create Custom Workflows in PostgreSQL
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.
1CREATE OR REPLACE PROCEDURE insert_funds(IN amount INT, IN account_id INT)
2 BEGIN
3 -- ...
4 END;
5$ 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.
1CREATE OR REPLACE PROCEDURE add_funds(IN in_amount INT, IN in_account_id INT)
2 BEGIN
3 UPDATE account
4 SET total = total + in_amount
5 WHERE account_id = in_account_id;
6
7 INSERT INTO transaction_history(is_credit, amount, account_id)
8 VALUES(TRUE, in_amount, in_account_id);
9
10 COMMIT;
11
12 EXCEPTION WHEN OTHERS THEN
13 ROLLBACK;
14 END;
15$ 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:
1CALL 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.