Now Reading: How to Create Stored Procedures in PostgreSQL

Loading
svg
Open

How to Create Stored Procedures in PostgreSQL

January 15, 20246 min read

With PostgreSQL, it is possible to create stored procedures that define various routines that should be executed when called. These routines consist of SQL statements that perform defined operations on your database. For example, you can create a stored procedure that updates the values ​​in your table when called.

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:

CREATE OR ALTER PROCEDURE PROCEDURE_NAME(

parameters[s] data type

,

Language plpsql;

AS$$

announced

variable_if_any data_type

Start

logic

Ending;

,

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.

call procedure_name(args);

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.

conclusion

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.

How do you vote?

0 People voted this article. 0 Upvotes - 0 Downvotes.
svg

What do you think?

Show comments / Leave a comment

Leave a reply

Loading
svg