Procedure in PostgreSQL is a user-defined function that can be created using the list of parameters and the body of the function. The function body contains the steps that will be performed upon its execution to manipulate PostgreSQL tables. Calling the function by its name will result in its execution to complete the transactional operation and complete the process each time.
This guide will explain how to execute stored procedures with parameters in PostgreSQL.
How to Execute Stored Procedure With Parameters in PostgreSQL?
To execute the stored procedure with parameters in PostgreSQL, open the pgAdmin application from the local system:
Type the master password to connect to the PostgreSQL server and click on the “OK” button:
Head into the public Schema of the database and expand the procedures section to open it by right-clicking on it. Click on the “Properties” button from the options menu of the stored procedure:
Click here to learn how to create a procedure in PostgreSQL.
Head into the “Definition” section to get the argument list of the procedure:
Check the body of the procedure from the “Code” section which contains multiple steps to perform transactions. Calling it would take the amount from the receiver and adds it to the receiver’s account:
Use the following query to get the data from the accounts table:
SELECT * FROM accounts;
The above command displays the data stored in the table:
Calling a Procedure
Use the following command to call the stored procedure using its name and arguments:
call transfer(1,2,1000);
The above command uses the call keyword followed by the name of the stored procedure and then comes the list of the parameters. The parameter list contains three arguments which are the sender, receiver, and the amount separated by commas:
Use the following command to get the data stored in the accounts table after the transaction is complete:
SELECT * FROM accounts;
The following screenshot displays that 1000 is deducted from Ross’ account and added to Taylor’s account:
That’s all about executing a stored procedure with parameters in PostgreSQL.
Conclusion
To execute a stored procedure with parameters in PostgreSQL, open the pgAdmin application from the local computer and connect to the database. Open the properties of the procedure and get the parameters that will be used while calling the stored procedure. Use the “call” statement with the name and parameters of the procedure to execute the procedure in PostgreSQL.
