A Stored Procedure can be used like a modular programming where we must create procedure once, store it and call for several times whenever it is needed. Execution time is fast in stored procedures. Procedures reduce network traffic and provide security to the data.
In previous article we had seen user defined functions so let’s see few differences between Functions and Procedures.
Now we will see how to create and execute Procedures in PostgresSQL. PostgreSQL version 11 introduced stored procedures which support transactions, before that it was not possible to create stored procedures in PostgresSQL. From version 11 stored procedures added as a new schema object and which is similar object to stored function but without return value.
Syntax to Create Stored Procedure:
CREATE OR REPLACE PROCEDURE <Procedure_Name > (Parameter_List)
- Procedure_Name: is the name of the procedure, write after CREATE PROCEDURE keywords, after Procedure_Name you can give list of parameters separated by comma inside parentheses.
- Language_Name: Specify the procedural language of the procedure e.g., plpgsql in case PL/pgSQL is used.
- Code : or logic is placed inside the AS followed by $$ symbol
- Finally, use $$ to end the stored procedure.
Syntax to Execute a Stored Procedure:
CALL <Procedure_Name> (Parameter_List);
Example #1: Let’s Print a Notice
CREATE PROCEDURE Procedure_Ex(INOUT p TEXT)
RAISE NOTICE 'First Procedure Example: %', p ;
LANGUAGE plpgsql ;
CALL Procedure_Ex (' Hello World ');
NOTICE: First Procedure Example: Hello World
Example 2: How to use transactions in Stored Procedures.
CREATE OR REPLACE PROCEDURE Transaction_Ex()
CREATE TABLE Table_1 (id int);
INSERT INTO Table_1 VALUES (100);
CREATE TABLE Table_2 (id int);
INSERT INTO Table_2 VALUES (200);
To check how many tables are created, use below command:
This will give List of Relations
Schema | Name | Type |Owner
public | Table_1 | table | postgres
After command \d we can see Table_1 is created because the table was committed , the Table_2 has not been created because of the rollback inside the stored procedure.
Now check the value inserted into the table:
Example 3: Create SP which transfers a specified amount of money from one account to another.
Suppose we have Account_Table (Acc_No, Name, Balance) with 2 rows in it.
Acc_No | Name | Balance
101 | Jane | 20000
201 | Peter | 10000
Now let’s see an example to update above accounts.
CREATE OR REPLACE PROCEDURE Ttransfer_Money(INT, INT, DEC)
-- subtracting the amount from the sender's account
SET Balance = Balance - $3
WHERE AccNo = $1;
-- adding the amount to the receiver's account
SET Balance = Balance + $3
WHERE AccNo = $2;
Execute Stored Procedure:
Above statement transfer 2000 from an account with id 101 (Jane) to the account with id 201 (Peter).
Let’s verify the data in the given table
SELECT * FROM Account_Table;
Acc_No | Name | Balance
101 | Jane | 18000
201 | Peter | 12000
It’s all about Stored Procedures. We executed few examples by using PostgresSQL 11, So we can say procedures are nothing but the extended version of Stored functions with added functionality to handle transactions.