Stored procedures
For production, use the latest stable version (v2024.1).
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.
-
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;