Stored procedures

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;
$$;

{{< note title="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.

{{< /note >}}

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

{{% explore-setup-single-new %}}

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:

   sqlCopy

   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:

   sqlCopy

   select * from accounts;
   

   outputCopy

   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:

   sqlCopy

   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:

   sqlCopy

   call move_money(1,2,1000);
   

   sqlCopy

   select * from accounts;
   

   outputCopy

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

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

   sqlCopy

   drop procedure if exists move_money;
   

   sqlCopy

   drop table if exists accounts;