Introduction to PostgreSQL CREATE PROCEDURE statement

So far, you have learned how todefine user-defined functions using thecreate function statement.

A drawback of user-defined functions is that they cannot executetransactions. In other words, inside a user-defined function, you cannotstart a transaction, and commit or rollback it.

PostgreSQL 11 introduced stored procedures that support transactions.

To define a new stored procedure, you use thecreate procedure statement with the following syntax:

create [orreplace] procedure procedure_name(parameter_list)language plpgsqlas $$declare-- variable declarationbegin-- stored procedure bodyend; $$

In this syntax:

• First, specify the name of the stored procedure after thecreate procedure keywords.
• Second, define parameters for the stored procedure. A stored procedure can accept zero or more parameters.
• Third, specifyplpgsql 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 thein andinout modes but cannot have theout mode.

A stored procedure does not return a value. You cannot use thereturn statement with a value inside a store procedure like this:

return expression;

However, you can use thereturn statement without theexpression to stop the stored procedure immediately:

return;

If you want to return a value from a stored procedure, you can use parameters with theinout mode.

PostgreSQL CREATE PROCEDURE statement examples

We will use the followingaccounts table for the demonstration:

drop table if exists accounts;create table accounts (    idint generated by default as identity,    name varchar(100)not null,    balancedec(15,2)not null,    primary key(id));insert into accounts(name,balance)values('Bob',10000);insert into accounts(name,balance)values('Alice',10000);

The following statement shows the data from theaccounts table:

select * from accounts;

Output:

id | name  | balance----+-------+----------  1 | Bob   | 10000.00  2 | Alice | 10000.00(2 rows)

The following example creates a stored procedure namedtransfer that transfers a specified amount of money from one account to another.

create or replace procedure transfer(   sender int,   receiver int,   amount dec)language plpgsqlas $$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 theCALL statement as follows:

call stored_procedure_name(argument_list);

For example, this statement invokes thetransfer stored procedure to transfer$1,000 from Bob’s account to Alice’s account.

call transfer(1,2,1000);

The following statement verifies the data in theaccounts table after the transfer:

SELECT * FROM accounts;

Output:

id | name  | balance----+-------+----------  1 | Bob   |  9000.00  2 | Alice | 11000.00(2 rows)

The output shows that the transfer has been successful.

Summary

• Usecreate procedure statement to define a new stored procedure.
• Use thecall statement to invoke a stored procedure.

Last updated onFebruary 1, 2024

On this page

• Introduction to PostgreSQL CREATE PROCEDURE statement
• PostgreSQL CREATE PROCEDURE statement examples
• Calling a stored procedure
• Summary