PostgreSQL – CREATE PROCEDURE
A drawback of user-defined functions is that they cannot execute transactions. In other words, inside a user-defined function, you cannot start a transaction, and commit or rollback it. PostgreSQL 11 introduced stored procedures that support transactions. To define a new stored procedure, you use the create procedure statement.
The following illustrates the basic syntax of the create procedure statement:
Syntax: create [or replace] procedure procedure_name(parameter_list) language plpgsql as $$ declare -- variable declaration begin -- stored procedure body end; $$
Let’s analyze the above syntax:
- First, specify the name of the stored procedure after the create procedure keywords.
- Second, define parameters for the stored procedure. A stored procedure can accept zero or more parameters.
- Third, specify plpgsql as the procedural language for the stored procedure. Note that you can use other procedural languages for the stored procedure such as SQL, C, etc.
- Finally, use the dollar-quoted string constant syntax to define the body of the stored procedure.
Parameters in stored procedures can have the in and inout modes. They cannot have the out mode. A stored procedure does not return a value. You cannot use the return statement with a value inside a store procedure like this:
However, you can use the return statement without the expression to stop the stored procedure immediately:
We will use the following accounts table for the demonstration:
drop table if exists accounts; create table accounts ( id int generated by default as identity, name varchar(100) not null, balance dec(15, 2) not null, primary key(id) ); insert into accounts(name, balance) values('Raju', 10000); insert into accounts(name, balance) values('Nikhil', 10000);
The following query will show the table data:
select * from accounts;
That depicts the result as shown below:
The following query creates a stored procedure named transfer that transfers a specified amount of money from one account to another.
create or replace procedure transfer( sender int, receiver int, amount dec ) language plpgsql as $$ begin -- subtracting the amount from the sender's account update accounts set balance = balance - amount where id = sender; -- adding the amount to the receiver's account update accounts set balance = balance + amount where id = receiver; commit; end;$$
Calling a stored procedure
To call a stored procedure, you use the CALL statement as follows:
The below statement invokes the transfer stored procedure to transfer $1, 000 from Raju’s account to Nikhil’s account:
call transfer(1, 2, 1000);
The following statement verifies the data in the accounts table after the transfer:
SELECT * FROM accounts;