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

Create a stored procedure

Stored procedures, in large part, are just functions that support transactions. 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

Before you start

The examples will run on any YugabyteDB universe.
To create a universe, see Set up YugabyteDB universe.

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.

  1. 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)
    
  2. 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;$$;
  3. 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)
    
  4. Finally, clean up by removing the stored procedure and table:

    drop procedure if exists move_money;
    drop table if exists accounts;