skip to Main Content

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


  1. 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... ;
    
    Login or Signup to reply.
  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search