I want to create a procedure that receives two ids, makes two selects in a table and returns the data, after that I want to perform an update in another table using the result that was returned to me earlier, how to do that?
This is an example of how it is at the moment
create or replace procedure transfer(
origin int,
destination int,
amount dec
)
language plpgsql
as $$
begin
select id as id_user_origin
from users
where id = origin
select id as id_user_destination
from users
where id = destination
-- subtracting the amount from the sender's account
update wallets
set balance = balance - amount
where id = id_user_origin;
-- adding the amount to the receiver's account
update wallets
set balance = balance + amount
where id = id_user_destination;
commit;
end;$$
2
Answers
You need to store results of the different selects into variables:
You can reduce the procedure to a single DML statement.
The
exists
are not technically required, but guard against procedure receiving invalid parameters. See demo which includes a message where the update was not performed because of invalid user or insufficient balance. This, if included, would normally be written to a log table.