Summary: in this tutorial, you will learn how to use the PostgreSQL
CREATE PROCEDURE statement to create new stored procedures.
Introduction to PostgreSQL
CREATE PROCEDURE statement
So far, you have learned how to define user-defined functions
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:
create [or replace] procedure procedure_name(parameter_list) language plpgsql as $$ declare -- variable declaration begin -- stored procedure body end; $$
In this syntax:
- First, specify the name of the stored procedure after the
- Second, define parameters for the stored procedure. A stored procedure can accept zero or more parameters.
- Third, specify
plpgsqlas 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
inout modes. They cannot have the
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:
If you want to return a value from a stored procedure, you can use parameters with the
PostgreSQL CREATE PROCEDURE statement examples
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('Bob',10000); insert into accounts(name,balance) values('Alice',10000);
The following statement shows the data from the
select * from accounts;
The following example 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:
For example, this statement invokes the
transfer stored procedure to transfer
$1,000 from Bob’s account to Alice’s account.
The following statement verifies the data in the
accounts table after the transfer:
SELECT * FROM accounts;
It worked as expected.
create procedurestatement to define a new stored procedure.
- Use the
callstatement to invoke a stored procedure.