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