How to Create Stored Procedures in PostgreSQL

[Linux Hint]

With PostgreSQL, it’s possible to create stored procedures that define various routines that should be executed when called. These routines are comprised of SQL statements that perform the defined tasks on your database. For instance, you can create a stored procedure that updates the values in your table when you call it.

Stored procedures help with database optimization and in enhancing reusability. Instead of having to execute the same query, you can create the task as a stored procedure that you will call whenever required. You will learn everything about stored procedures by the end of this post.

Working with Stored Procedures in PostgreSQL

As a PostgreSQL user, you might have noticed that PostgreSQL functions don’t execute transactions. While it’s possible to create a transaction, committing it or rolling it back to the previous state is not possible. However, these limitations are bypassed using stored procedures.

Here’s the basic syntax to create a stored procedure in PostgreSQL:

CREATE OR REPLACE PROCEDURE procedure_name(

parameter[s] data_type

)

LANGUAGE plpsql;

AS $$

DECLARE

variables_if_any data_type

BEGIN

logic

END;

$$

The key things to note from the given syntax are the “procedure_name” which is the name that you will use for the stored procedure, the parameters that you wish to include and their data types, and the logic which are mainly the SQL statements.

Let’s give three examples to help you understand how to create the stored procedures in PostgreSQL.

Example 1: A Stored Procedure to Calculate the Square of a Number

For our first example, we create a stored procedure that uses the “RAISE NOTICE” statement as a way of printing the output to the terminal. The stored procedure takes the integer value that you give it when calling it and calculate its square.

Here’s how we create the stored procedure:

We name our parameter as “num1”, and it’s an integer. For the logic part, we define how it gets the square of “num1” and stores it as the square 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 give it the expected argument.

CALL procedure_name(arguments);

You will get the CALL output showing that the stored procedure has been executed, and we are getting the expected output which, in this case, is the square of the argument that we added.

Example 2: A Stored Procedure to Insert the 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 “students” table that we will work with.

For this example, we create a stored procedure that allows a user to insert the values into the newly created table. Notice how we specify the parameters that we expect to be added as arguments when we call the stored procedure. Moreover, we define the logic that takes the added arguments and executes an INSERT SQL statement to the “students” table.

We can check the available stored procedures by running the following command:

\df

The first stored procedure that we can see from the following output is the “add_student” that we previously created.

Now, let’s 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 that we added with the call procedure command are the values for our first student in our table. That’s how you create a stored procedure to insert the values into a table.

Note that when creating the stored procedure, the parameters that you specify must match what is expected in your table to avoid errors. Moreover, the data type must match.

Example 3: A Stored Procedure to Update a Table Entry

Moving on, let’s create another stored procedure that updates a table entry. If you want to have a quick way of updating the values in our table, you could create an update stored procedure as follows:

Specify which column you want to update using the WHERE keyword and the new value using the SET keyword. You must then add the COMMIT keyword to persist 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 course for the particular student that we targeted. That’s how an update stored procedure works.

Conclusion

You can create any stored procedure in PostgreSQL. You only 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 has executed as expected. This post explained about the stored procedures in PostgreSQL and provided examples on how to create them.

View the full article