SQL for QA: Tutorial#12 – Stored Procedure

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 xProcedure_Name x (Parameter_List)
LANGUAGE Language_Name
AS 
$$
xStored_Procedure_Bodyx;
$$;

• 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 xProcedure_Namexxnbsp; (Parameter_List);

Example #1:  Letxs Print a Notice     

CREATE PROCEDURE Procedure_Ex(INOUT p TEXT)
AS $$
BEGIN
RAISE NOTICE 'First Procedure Example: %', p ;
END ;
$$
LANGUAGE plpgsql ;

Execute Procedure:

CALL Procedure_Ex (' Hello World ');

Output:

NOTICE:  First Procedure Example:  Hello World

Example 2: How to use transactions in Stored Procedures.

CREATE OR REPLACE PROCEDURE Transaction_Ex()
LANGUAGE plpgsql
AS $$
DECLARE
BEGIN
 CREATE TABLE Table_1 (id int);
 INSERT INTO Table_1 VALUES (100);
 COMMIT;
 CREATE TABLE Table_2 (id int);
 INSERT INTO Table_2 VALUES (200);
 ROLLBACK;
END $$;

Execute Procedure:

CALL Transaction_Ex();

Output:

To check how many tables are created, use below command:

postgres=# \d

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:

SELECT * FROM Table_1;

Output:

 id
 ----
 100

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)
LANGUAGE plpgsql    
AS $$
BEGIN
  -- subtracting the amount from the sender's account

UPDATE Account_Table
SET Balance = Balance - $3
WHERE AccNo = $1;

  -- adding the amount to the receiver's account

UPDATE Account_Table
SET Balance = Balance + $3
WHERE AccNo = $2;

COMMIT;

END;

      $$;

Execute Stored Procedure:

CALL Ttransfer_Money(101,201,2000);

Above statement transfer 2000 from an account with id 101 (Jane) to the account with id 201 (Peter).

Output:

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.