This section describes how to use stored procedures to perform transactions.
Stored procedures, in large part, are simply functions that support transactions. PostgreSQL 11 introduced stored procedures, and Yugabyte supports them as well.
Creating 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; $$;
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
Invoking a Stored Procedure
To invoke a stored procedure, use the
CALL statement, which has the following syntax:
For example, drawing from the Example workflow on this page:
yugabyte=# call move_money(1,2,1000);
Deleting 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 ]
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.
In the following example, you'll create a new table and a stored procedure to perform operations on that table. Finally, you'll clean up by removing the procedure and the table.
accountstable 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:
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;