Stored procedures

This section describes how to use stored procedures to perform transactions.

Overview

Stored procedures, in large part, are just functions that support transactions. PostgreSQL 11 introduced stored procedures, and Yugabyte supports them as well.

Create a stored procedure

To create a stored procedure in YSQL, use the CREATE PROCEDURE statement, which has the following syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name(parameter_list)
LANGUAGE SQL
AS $$
DECLARE
-- variable declaration
BEGIN
-- stored procedure body
END;
$$;

Using return

Stored procedures don't return any values, other than errors. In a function, you use return <expression> to return a value. In a stored procedure, return does not support an expression, and ends the procedure immediately.

To return a value from a stored procedure, use an INOUT parameter.

Invoke a stored procedure

To invoke a stored procedure, use the CALL statement, which has the following syntax:

CALL stored_procedure_name(argument_list)

For example, drawing from the Example workflow on this page:

yugabyte=# call move_money(1,2,1000);

Delete a stored procedure

To remove a stored procedure, use the DROP PROCEDURE statement, which has the following syntax:

DROP PROCEDURE [IF EXISTS] stored_procedure_name(argument_list)
    [ CASCADE | RESTRICT ]

For example,

yugabyte=# drop procedure move_money(integer, integer, decimal);

If the name of the stored procedure is not unique (for example, if you had two insert_data() procedures, one of which accepted two integers and another which accepted an integer and a varchar), you must specify the data types in the DROP PROCEDURE statement. Otherwise, you can omit the data types.

Example workflow

In the following example, you create a new table and a stored procedure to perform operations on that table. Finally, you clean up by removing the procedure and the table.

Create an accounts table with two users, and set the balance of both accounts to $10,000:

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('User1',10000);

insert into accounts(name,balance)
values('User2',10000);

Make sure the creation and insertions worked:

select * from accounts;

id | name  | balance
----+-------+----------
  1 | User1 | 20000.00
  2 | User2 | 20000.00
(2 rows)

Create a stored procedure to move money from one account to another:

create or replace procedure move_money(
  origin integer,
  destination integer,
  amount decimal
)
language plpgsql
as $$
begin
    -- subtracting the amount from the origin account
    update accounts
    set balance = balance - amount
    where id = origin;

    -- adding the amount to the destination account
    update accounts
    set balance = balance + amount
    where id = destination;

    commit;
end;$$;

Move $1000 from the first account to the second, then make sure it worked:

call move_money(1,2,1000);

select * from accounts;

id | name  | balance
----+-------+----------
  1 | User1 | 19000.00
  2 | User2 | 21000.00
(2 rows)

Finally, clean up by removing the stored procedure and table:

drop procedure if exists move_money;

drop table if exists accounts;