Stored procedures help increase database optimization and reusability. Instead of executing a single query, you can create the task as a stored procedure that you will call when needed. You will learn all about stored procedures by the end of this post.
Working with Stored Procedures in PostgreSQL
As a PostgreSQL user, you may have noticed that PostgreSQL functions do not execute transactions. Although it is possible to create a transaction, it is not possible to commit it or revert it to a previous state. However, these limitations are circumvented by using stored procedures.
Here is the basic syntax for creating a stored procedure in PostgreSQL:
parameters[s] data type
The main things to note from the given syntax are the “procedure_name” which is the name you will use for the stored procedure, the parameters you want to include and their data types, and the arguments which are mainly SQL statements.
Let's give three examples to help you understand how to create stored procedures in PostgreSQL.
Example 1: Stored Procedure to Find the Square of a Number
For our first example, we create a stored procedure that uses the “RAISE NOTICE” statement as a way to print output to the terminal. The stored procedure takes the integer value that you give when you call it and calculates its square.
Here's how we create a stored procedure:
We name our parameter “num1”, and it is an integer. For the logic part, we define how it gets the class of “num1” and stores it as a class variable. When we execute the command, we get the “Create Procedure” output which confirms that we managed to create the stored procedure successfully.
The next task is to call the procedure and pass it the required arguments.
You will get the CALL output which will show that the stored procedure has been executed, and we are getting the expected output, which in this case, is the class of the argument that we added.
Example 2: A stored procedure to insert values into a table entry
The following two examples show how to create a stored procedure that works with a database table. Let's quickly create the “Student” table that we will work with.
For this example, we create a stored procedure that allows the user to insert values into the newly created table. Notice how we specify the parameters we expect to be added as arguments when we call the stored procedure. Additionally, we define the logic that takes the added arguments and executes the INSERT SQL statement into the “students” table.
We can check the available stored procedures by running the following command:
The first stored procedure that we can see from the following output is “add_student” which we created earlier.
Now, call the stored procedure to execute it. The following image shows how we have an empty table, but we called the stored procedure to add the first student:
If we list the values in our table, notice how the arguments we added with the call procedure command are the values for our first student in our table. This is how you create a stored procedure to insert values into the table.
Note that when creating a stored procedure, the parameters you specify must match those expected in your table to avoid errors. Furthermore, the data types must match.
Example 3: A stored procedure to update a table entry
Moving on, let's create another stored procedure that updates the table entry. If you want a quick way to update the values in our table, you can create an UPDATE stored procedure as follows:
Specify which column you want to update using the WHERE keyword and update the new value using the SET keyword. You need to add COMMIT keyword to commit the changes.
Let's call the UPDATE stored procedure and add the expected arguments: “student_id” and the new course.
If we list the entries in our table, we can verify that we have the updated curriculum for the particular student we have targeted. The UPDATE stored procedure works similarly.
You can create any stored procedure in PostgreSQL. You need to understand the syntax to follow and then define your logic for the stored procedure. From there, call the stored procedure and verify that it executed as expected. This post explains stored procedures in PostgreSQL and gives examples of how to create them.