Postgresql stored procedure example

Postgresql database is more reliable than others because it can create and store the data in the form of relations, procedures, etc. Postgresql is a customizable database, as we can modify the storage containers according to our requirements. The data in Postgresql is managed by the schemas and catalogs. Postgresql supports many languages, which means that we can execute queries in any programming language either in the psql (shell) or on the pgAdmin side.

Just like the temporary tables, we also use some other features to involve the storage capacity. These are called the “STORED PROCEDURES”. These are not shown like the tables. But silently works with the tables.

In Postgresql or any other database management system, we use functions to perform operations on the data. These functions are user-created or user-defined. One major drawback of these functions is that we are unable to execute transactions inside the functions. We cannot commit or rollback. That’s why we use the stored procedures. By using these procedures, application performance is increased. Moreover, we can use more than one SQL statement inside a single procedure. There are three types of parameters.

IN: It is the input parameter. It is used to insert the data from the procedure into the table.

OUT: It is the output parameter. It is used to return the value.

INOUT: It represents both input and output parameters. As they can pass and returns the value.

SYNTAX

Install Postgresql in your system. After successful configuration, we are now able to access the database. We have two choices to apply the queries. One is psql shell, whereas the other one is the pgAdmin dashboard. We have used pgAdmin for this purpose. Open the dashboard, now provide the password to maintain the connection with the server.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/1-52.jpg" data-lazy- height="270" src="data:image/svg xml,” width=”656″>

Procedure Creation

To understand the working of the stored procedure, we need to create a relationship by using a create statement.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/2-49.jpg" data-lazy- height="251" src="data:image/svg xml,” width=”534″>

Usually, we enter the values in the table by using the “insert” statement, but here we use a stored procedure that will use as the temp table. Firstly the data will be stored in them, and then they will further transfer the data in the table.

Create a stored procedure name “Addclothes”. This procedure will act as a medium between the query and the table. Because all the values are first inserted in this procedure and are then inserted through the insert command directly to the table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/3-48.jpg" data-lazy- height="466" src="data:image/svg xml,” width=”578″>

Now the values from the stored procedure are entered in the table clothes. From the query, it is clear that first, we have defined the store procedure with the attribute of slightly different column names with the same data types. Then, using an insert statement, the values from the values of the stored procedure are entered into the table.

Just like a simple function, we use a function call to send the values as arguments in the parameter so that the procedure accepts these values.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/4-44.jpg" data-lazy- height="279" src="data:image/svg xml,” width=”789″>

As the name of the procedure is “Addclothes”, so it is written with the values in the same way as we directly write them in the insert statement. The output is shown as 1; as we used the returning method, this shows that one row is filled. We will see the data inserted by using a select statement.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/5-39.jpg" data-lazy- height="265" src="data:image/svg xml,” width=”615″>

Repeat the above procedure up to the extent you want to enter the values.

Stored Procedure and the “UPDATE” clause

Now create the procedure to update the already existed data in the table “clothes”. The first part of the query in entering values to the stored procedure is the same.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/6-34.jpg" data-lazy- height="407" src="data:image/svg xml,” width=”494″>

Now we will call the stored procedure. The call syntax is the same, as it only uses the values in the parameters as arguments.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/7-30.jpg" data-lazy- height="210" src="data:image/svg xml,” width=”746″>

A message is displayed that shows that the query is successfully executed with the time of execution. Use the select statement to fetch all the records to see the values that are replaced.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/8-31.jpg" data-lazy- height="228" src="data:image/svg xml,” width=”657″>

The procedure with the“DELETE” clause

The next store procedure we will use here is the “deleteclothes”. This procedure will get only the id as an input, and then the variable is used to match the id with the id present in the table. When the match is found, the row is deleted respectively.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/9-26.jpg" data-lazy- height="366" src="data:image/svg xml,” width=”740″>

Now we will call the procedure. This time only a single id is used. This id will locate the row that is to be deleted.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/10-24.jpg" data-lazy- height="206" src="data:image/svg xml,” width=”418″>

The row having the id “2” will be deleted from the table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/11-22.jpg" data-lazy- height="233" src="data:image/svg xml,” width=”624″>

There were 3 rows in the table. Now you can see that only two rows are left because the row with “2” id is deleted from the table.

Function Creation

After the complete discussion of the stored procedure, we will now consider how the user-defined functions are introduced and used.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/12-19.jpg" data-lazy- height="316" src="data:image/svg xml,” width=”641″>

A function is created with the same name as the stored procedure. All the data from the table “clothes” is displayed in the result data output portion. This return function will not take any argument in the parameter. By using this function, we got the data as shown above in the image.

The other function is used to get the clothes data from the specific id. A variable in the integer is introduced in the parameter. This id will be matched with the id in the table. Where the match is found, the specific row is displayed.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/13-16.jpg" data-lazy- height="291" src="data:image/svg xml,” width=”586″>

As an argument, we will call the function having the id we want to fetch the records from the table.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/10/echo/14-10.jpg" data-lazy- height="208" src="data:image/svg xml,” width=”631″>

Hence from the output, you can see that only a single row is fetched from the table “clothes”.

Conclusion

“Postgresql stored procedure example” elaborates the examples with the creation and operations of the procedures. Functions had a drawback that was removed by the Postgresql stored procedures. Examples regarding procedures and functions are elaborated that will be sufficient for gaining knowledge regarding the procedures.