How to create procedures with two different selects?-CodePudding

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

CodePudding user response：

You need to store results of the different selects into variables:

declare
    id_user_origin int;
begin
    select id into id_user_origin from users where id = origin ;
     
  .....
  update... ;

CodePudding user response：

You can reduce the procedure to a single DML statement.

create or replace procedure transfer(
      origin int
    , destination int  
    , amount dec
)
language plpgsql   
as $$
begin
    update wallets 
       set balance = case when id = origin 
                          then balance - amount
                          else balance   amount
                     end 
      where id in (origin, destination) 
        and exists (select null from wallets where id = destination)
        and exists (select null from wallets where id = origin and balance >= amount);
 
    commit; 
end ;
$$;

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.