Kevin McGahey - January 9, 2024
Introduced in PostgreSQL 11, these procedures are a game-changer for database efficiency, allowing for pre-compiled and reusable SQL code. In this article, we’ll how to create PostgreSQL stored procedures, a key tool for scaling up and streamlining database operations. Whether you’re a novice or an expert in PostgreSQL, this guide offers valuable insights into maximizing the effectiveness of stored procedures in your database management. Let’s dive into the practicalities and advantages of using PostgreSQL Stored Procedures.
Here’s the 3 key things to know about creatinig a PostgreSQL stored procedure:
- PostgreSQL Stored Procedures are pre-saved SQL code, introduced in PostgreSQL 11, enhancing database efficiency and team productivity by eliminating redundant coding.
- Stored procedures improve performance through one-time compilation and enable executing grouped SQL statements in a single call, speeding up response time.
- The article also covers how to view and call these stored procedures within PostgreSQL, illustrating with real-life examples for better understanding.
Table of Contents:
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.
How to Create a PostgreSQL Stored Procedure
In this section, we offer a straightforward, step-by-step guide to help you achieve a specific task or find a solution. These clear and concise instructions are designed to simplify your journey, making it easier to navigate through the process. Let’s get started on tackling this task or solving this problem together.
1. Inserting Data to 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
2. Adding a New Record
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.
3. Viewing your Stored Procedure
To view your stored procedures run the following command.
\df
4. 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;
Getting Started with 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!
Kevin McGahey is an accomplished solutions engineer and product lead with expertise in API generation, microservices, and legacy system modernization, as demonstrated by his successful track record of facilitating the modernization of legacy databases for numerous public sector organizations.