PostgreSQL – CREATE PROCEDURE

PostgreSQL CREATE PROCEDURE statement as the name suggests is used to create new stored procedures. So far, you have learned how to define user-defined functions using the create function statement.

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:

return expression;

However, you can use the return statement without the expression to stop the stored procedure immediately:

return;

Example:

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:

call stored_procedure_name(argument_list);

Example:

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;

Output:

Article Tags :

PostgreSQL

postgreSQL-stored-procedures