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;
$$;
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
Setup
Any YugabyteDB cluster, including local single-node cluster or Sandbox cluster in YugabyteDB Managed. 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;