In this article, we will learn how to work with procedures in Azure Database for PostgreSQL.
Introduction
Data is typically stored in tables in any database management system. Data is queried typically using the query language supported by the database. Applications that consume data from these tables can typically execute ad-hoc queries on the database to read and write data. Ad-hoc queries are a reasonable approach when analyzing data, but when the queries are expected to be executed in an industrialized fashion, compiled routines of query logic are employed as the query plan is optimized for execution. Additionally, it becomes a standard façade for decoupling actual data sources from the consumer application. These routines are called procedures in the database parlance. Azure Database for PostgreSQL is the Postgres database offering on the Azure cloud.
Procedures in Azure Database for PostgreSQL
It is assumed that one already has an Azure account with an instance of Azure Database for PostgreSQL setup on it. To operate in this instance, we typically need a management tool. pgAdmin is one of the most popular tools that is typically used with PostgreSQL. It is assumed that one has already installed this tool and connected it to the instance of Azure Database for PostgreSQL, as we would be using it for working with procedures.
The first thing to understand before starting to develop and define procedures in Azure Database for PostgreSQL is the syntax of procedures. CREATE PROCEDURE is the SQL statement that is used to create procedures in Azure Database for PostgreSQL, and the syntax for the same is as mentioned below.
Let us understand different parts of this syntax before proceeding with an example of implementing the procedure.
- name – This parameter represents the name of the procedure.
- argmode – This parameter represents whether mode of parameter, whether it is an input or input and output parameter.
- argname – This parameter represents the name of the parameters that would be passed to the procedure while invoking it.
- argtype – This parameter represents the data type of the parameter.
- default_expr – This parameter represents the expression for the default value of the specified parameter.
- lang_name – This parameter represents the language name that the procedure is implementing.
- TRANSFORM – This part lists and custom or user-defined transformations to handle any custom data type that the user may have defined.
- SECURITY INVOKER | SECURITY DEFINER – These parts represent the privileges of the user using which the procedure should be executed.
- SET – This section and the options in this section represent the syntax to set to the value of the variables as well as system parameters.
Now that we understand the syntax of creating procedures, let’s proceed with an example of implementing a procedure in the Azure Database for PostgreSQL. We need to have at least one table with some records in it so that we can use the stored procedure to perform some action on this table. As shown below we have created one such table named employee and have inserted a few records in it.
Assuming pgAdmin is open and we are using it for developing procedures in Azure Database for PostgreSQL, navigate to the procedure section in the browser pane of pgAdmin, right-click and select the menu option to create a new procedure. Here we are using the graphical interface provided by pgAdmin to create a new procedure instead of coding it from scratch. The procedure creation wizard would look as shown below. The first step is to provide a name for the procedure. We intend to use this procedure to insert data into the employee table, so we have named it InsertEmpData. By default, the owner would be the user id using which one has connected to the database instance and the default schema would be public. Configure these options as desired, and then click on the Definition tab.
In the Definition tab, we can define the parameters that would be used by the procedure for input and output. Let’s say that we intend to pass two parameters – employee id and employee name. Click on the plus button to add parameters, select the data type of the parameter, provide an appropriate name, select the type of the parameter – input / input-output and optionally and default values for it. Once done, click on the Code section.
This section contains the body of the procedure where we define the actual logic that should be executed when the procedure is invoked. We intend to insert a record in the employee table using the input parameter employee id and name. We will use the INSERT command to insert data into the table, and the input parameter names would be passed in the value part of the command. Some query languages use the “@” prefix with parameters. In this case, in PostgreSQL, we do not need this prefix. So, key in the SQL statement as shown below and then click on the options tab.
In this section, we can configure a variety of options like whether to enforce strict definition and casing, whether to use the security of the user who is defining the procedure, and other such options. This is an optional step, and we can continue with the default values here.
In the next tab i.e., the Parameters tab, we can optionally select the system parameters and set their value. When we click on the button to add a new system parameter, we can select from the list of system parameters and set the desired values. As this is an optional step, we will skip configuring any system parameters and will continue with the next tab.
In the Security tab, we can grant privileges to different users and roles. If we expand the grantee drop-down, we will be able to view the list of users and roles as shown below. The privileges section allows setting the type of privilege to be granted to the selected user or role. For now, we will skip configuring options in this tab and navigate to the SQL tab.
After all these configurations, the SQL code to generate the procedure would look as shown below. Click on the Save button to execute this code and create a new procedure as shown below.
Once the procedure is created, it would be listed in the browser pane under the procedures section as shown below. Now that the procedure is in place, we can execute this procedure to test whether it works as expected. Right-click on the procedure and select the execute script option to generate the code to execute this procedure. It would generate the code as shown below. We must provide the parameter values that we want to use with the procedure. Change the values of the input parameters as shown below and then click on the Execute button to invoke the procedure.
Once the procedure is executed successfully, we can query the actual table to verify whether the procedure resulted in the addition of a new record in the employee table. If everything works as expected, then we should be able to find the new record in the table as shown below.
In this way, we can use the graphical interface of pgAdmin to create and maintain procedures in a structured manner.
Conclusion
In this article, we started with a setup of Azure Database for PostgreSQL instance, connected to it using pgAdmin, and created a basic table with few records. We learned the syntax of creating new procedures and then developed a parameterized procedure to insert records in a new table.
She has a deep experience in designing data and analytics solutions and ensuring its stability, reliability, and performance. She is also certified in SQL Server and have passed certifications like 70-463: Implementing Data Warehouses with Microsoft SQL Server.
View all posts by Gauri Mahajan
- Introduction to the SQL Standard Deviation function - April 21, 2023
- A quick overview of MySQL foreign key with examples - February 7, 2023
- Overview of the SQL Median function - January 4, 2023