What is a PostgreSQL Stored Procedure?
A stored procedure is SQL code that you can save and reuse. Stored procedures provide many advantages such as performance advantages due to them being compiled once, grouping SQL statements allows them to be executed in a single call to improve response time, and increase your team’s productivity since they no longer have to write redundant code. In PostgreSQL 11 PROCEDURE was introduced as a new schema object, previously there was no native support for stored procedures. This now gives PostgreSQL users the ability to utilize this widely popular database at scale. In this blog we will explore how to create, execute, and modify stored procedures within PostgreSQL.
Creating a PostgreSQL Stored Procedure
Throughout our examples we will be using the Pagila example data set so you can follow along. First we can start off with inserting data with a simple stored procedure. To do so we will need some additional information regarding the data types to abide by the schema. We can run the following command to list all the columns and their corresponding types that we will use for reference.
\d staff
We will then be adding a new record to the “staff” table with the stored procedure name of “sample_staff_insert”. The create command will look like the one shown below by giving it a name, the columns data types, and the values we will fill in.
CREATE PROCEDURE sample_staff_insert(_first_name text, _last_name text, _address_id smallint, _email text, _store_id smallint, _active boolean, _username text, _password text) LANGUAGE SQL AS $BODY$ INSERT INTO staff(first_name, last_name, address_id, email, store_id, active, username, password) VALUES(_first_name, _last_name, _address_id, _email, _store_id, _active, _username, _password); $BODY$;
When you execute the command you should see CREATE PROCEDURE returned letting you know the procedure was created properly.
To view your stored procedures run the following command.
\df
Calling a PostgreSQL Stored Procedure
Now that we have established our stored procedure, let’s put it to use! To use our stored procedures we will use CALL, the name of the stored procedure, and then provide the values we want to insert in the “staff” table.
CALL sample_staff_insert('John'::text, 'Snow'::text, 6::smallint, '[email protected]'::text, 2::smallint, 't'::boolean, 'John'::text, '8cb2237d0679ca88db6464eac60da96345513964'::text);
Once successfully executed you will have CALL returned to you letting you know that the record has been added. We can perform the following command to list the staff data ensuring the record was inserted properly.
SELECT * FROM staff;
DreamFactory and PostgreSQL
Connecting your application to Postgres can be a time consuming process. Building the custom API, creating documentation, and securing the connection can lead to a long development cycle. DreamFactory is able to help manage your existing APIs and instantly generate custom REST APIs for your PostgreSQL database while ensuring your data is secure at all times. You can even use your stored procedures via the API with no coding required!
Check out the advanced database features of our guide and start your free hosted trial of DreamFactory today!