PostgreSQL: Stored Procedure



The stored procedure is introduced in PostgreSQL 11 that supports the transaction. Postgres user-defined function has a drawback, it can not execute transactions. It means, in the function, we can not open a new transaction, even commit or rollback.

Syntax to create a stored procedure in PostgreSQL



CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE language_name
AS $$
    stored_procedure_body;
$$;


1- procedure_name is the procedure name.
2- parameter_list is the arguments you want to pass in the procedure.
3- language_name, here you will specify the language such as plpgsql or sql.
4- stored_procedure_body, here you will place the PostgreSQL query/statements.

It is not mandatory a PostgreSQL procedure returns a value like user-defined function. You can use output parameters to return value. If you are familiar with SQL Server the concept of PostgreSQL stored procedure is the same as the SQL Server stored procedure.

Create a stored procedure


For the example let me create a table and insert some records.


CREATE TABLE myaccount (
    acc_id INT GENERATED BY DEFAULT AS IDENTITY,
    acc_name VARCHAR(100) NOT NULL,
    balance DEC(15,2) NOT NULL,
    PRIMARY KEY(acc_id)
);

INSERT INTO myaccount(acc_name,balance)
VALUES('Dilip',10000);

INSERT INTO myaccount(acc_name,balance)
VALUES('Mamta',10000);



SELECT * FROM myaccount;



Result

CREATE OR REPLACE PROCEDURE usp_transfer(INT, INT, DEC)
LANGUAGE plpgsql   
AS $$
BEGIN
    -- subtracting the amount from the Dilip's account
    UPDATE myaccount
    SET balance = balance - $3
    WHERE acc_id = $1;

    -- adding the amount to the Mamta's account
    UPDATE myaccount
    SET balance = balance + $3
    WHERE acc_id = $2;

    COMMIT;
END;
$$;


  

Call the stored procedure in PostgreSQL


Syntax


CALL stored_procedure_name(parameter_list);


stored_procedure_name is the procedure name
parameter_list is the value of the argument whatever defined in the stored procedure.

                                 
select * from myaccount




No comments:

Post a Comment

Please do not enter any spam link in the comment box.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...