Database automation with Postgres stored procedures

Postgres, also known as PostgreSQL, is a powerful open-source relational database management system that has gained popularity for its scalability, robustness, and extensibility. It's widely used in enterprise-level applications, web development, and data analytics.

One of Postgres' most useful features is its ability to define and execute stored procedures. A stored procedure is a database automation tool that can execute a predefined set of database operations in a single call. In other words, stored procedures are collections of SQL or procedural language statements, like the Postgres-specific PL/pgSQL, stored on the database server and executed within the database environment.

Stored procedures offer numerous advantages for both database developers and administrators. One of the most significant benefits is reusability, as stored procedures can be invoked from within other procedures, functions, or queries. Stored procedures can also improve database performance by reducing network traffic, minimizing query parsing, and optimizing query execution.

Additionally, stored procedures can improve database security by limiting direct access to the underlying data. Instead of granting direct access to database tables, client applications can be restricted to calling stored procedures that have access to specific data sets, which can help prevent unauthorized access and data breaches.

In this article, we'll walk through how to automate database operations with Postgres stored procedures. We'll also cover the basics of creating, executing, and managing stored procedures in Postgres.

How to create stored procedures in Postgres

This tutorial provides a practical example of how to create a stored procedure that performs sum aggregation on sales data for individual products within a database. We will walk through the procedure of establishing a sales table, inserting dummy data, and subsequently creating and executing a stored procedure that aggregates the sales data.

Furthermore, we'll discuss the syntax for creating stored procedures and its key components, such as the CREATE PROCEDURE statement, the naming convention, the parameter declaration, the LANGUAGE clause, and the use of delimiters and variable declaration.

Stored procedure syntax

The syntax for creating a stored procedure in PostgreSQL is as follows:

In the syntax shown above:

• The CREATE [OR REPLACE] PROCEDURE statement creates a new stored procedure. We can optionally include the OR REPLACE clause to update an existing procedure with the same name.
• procedure_name is the name we specify for the procedure. It should be unique within the database schema.
• We can then specify the input and output parameters for the procedure. We must define each parameter with a name, a data type, and an optional IN, OUT, or INOUT keyword to specify whether the parameter is an input, an output, or both.
• LANGUAGE language_name signifies the procedural language for writing the procedure. PostgreSQL supports several languages, including SQL, PL/pgSQL (PostgreSQL's procedural language), PL/Python, PL/Perl, and so on.
• $$ indicates the delimiter used to enclose the procedure body. Using the delimiter is optional, but it's recommended that we do so to avoid issues with string literals in the procedure body.
• DECLARE is also an optional section of the stored procedure that defines variables used within the procedure. Variables are declared using the syntax variable_name data_type.
• The BEGIN and END delimiters define the beginning and end of the SQL statements used in the stored procedure.

Creating a stored procedure

Now that we walked through the syntax and format of a stored procedure, we'll put this into practice using an example. This example showcases the automation of a repetitive task that involves sum aggregation for the sales of each product in a database.

Before creating the stored procedure, we first need to create a sales table with some dummy data for the stored procedure to act upon. Execute the following query to create the sales table and insert the dummy data:

Now that the sales table has been created and dummy data inserted, we'll create and execute a stored procedure that aggregates the total sum of each product in the sales table into a temporary table called sales_summary. The stored procedure looks like this:

This stored procedure drops the temporary table sales_summary if it exists. It then creates a new temporary table that contains the unit sold and revenue calculation for each sale.

To execute the stored procedure, use the CALL command, which executes the sales_summary() stored procedure and generates a temporary table named sales_summary that contains the summarized sales data:

Then, run the following command to view the results:

Creating a dynamic stored procedure

The above example does not accept parameters, meaning that it isn't dynamic. We'll now enhance the previous stored procedure example by making it dynamic using the following:

In this enhanced version, the sales_summary() stored procedure accepts two input parameters, start_date and end_date. With these two newly added parameters, we can specify the duration for which we want the sales summary.

Execute the stored procedure using the CALL statement:

Other use cases for stored procedures

The previous sections explained stored procedures using a sample repetitive task that can be useful for inventory management. This section looks at additional repetitive tasks that can be performed with stored procedures.

Backup and restore

Backing up and restoring a database is time-consuming and error-prone. It requires careful attention to details like file names, directory paths, and data formats. However, with stored procedures, users can automate the backup and restore processes of a database while reducing the risk of errors and data loss.

For instance, they can create a stored procedure that takes a database name and a backup file path as input parameters and uses the PostgreSQL pg_dump utility to create a backup of the database to the file specified. On the other hand, to restore the database, they can also create another stored procedure that takes a database name and a backup file path as input parameters and uses the PostgreSQL pg_restore utility to restore the database from the specified file.

Using stored procedures to automate database backup and restore processes will ensure that backups are performed consistently and accurately. It also ensures that database restores are performed quickly and reliably in the event of data loss or corruption.

User and permission management

Managing user accounts and permissions can be complicated and time-consuming, especially if teams manage many users and complex permission structures in a large organization. With stored procedures, users can automate user and permission management tasks and reduce the risk of errors and security breaches. For example, they can create a stored procedure that takes a username and a password as input parameters and uses the PostgreSQL CREATE USER command to create a new user account with the specified credentials. Additionally, they can then create another stored procedure that takes a username and a database name as input parameters and uses the PostgreSQL GRANT command to grant the specified user access to the specified database.

Stored procedures can ensure that user accounts are created and permissions are granted consistently and securely across a database.

Managing large data sets

Similar to backing up and restoring databases, managing large data sets requires careful attention to details such as data formats, data integrity, and data consistency. With stored procedures, users can automate the process of inserting, deleting, and updating data in large data sets and reduce the risk of errors and data corruption. For example, they can create a stored procedure that takes a table name and a list of data rows as input parameters and uses the PostgreSQL INSERT command to insert the specified rows into the specified table.

They can also create another stored procedure that takes a table name and a list of key-value pairs as input parameters and uses the PostgreSQL UPDATE command to update the specified rows in the specified table. Using stored procedures to automate data management tasks ensures that data is inserted, updated, and deleted consistently and accurately and that data consistency and integrity are maintained across all tables and databases.

Although stored procedures can benefit users in several ways, there are some downsides to using them for automating repetitive jobs. Stored procedures are difficult to debug and maintain. Increasing the number and complexity of stored proceeds increases and amplifies memory consumption.

Luckily, there is an easier, more straightforward way to automate repetitive tasks in Postgres.

Introducing Airplane: an efficient way to automate recurring operations

Airplane is the developer platform for building custom internal tools. You can transform scripts, queries, APIs, and more into powerful UIs and workflows. The basic building blocks of Airplane are Tasks, which are single or multi-step operations that anyone can use. Airplane also offers Views, which are powerful, React-based custom UIs.

Airplane also offers maintenance-free and serverless job scheduling. With this powerful feature, you can schedule recurring jobs within Airplane's intuitive UI in either cron syntax or simple syntax. You can also use code to schedule tasks easily.

Using Airplane's strong built-in capabilities, such as audit logs and permissions setting, makes it easy to quickly schedule your most critical operations and track executions securely.

To try it out and schedule your first operation using Airplane, sign up for a free account or book a demo.