How To Create And Call A Stored Procedure With OUT Parameters In Postgre...

Only 3.9% of viewers are subscribing to my channel 😓.

I request you to please give click on Subscribe button.

It really helps me grow 😢.

create or replace procedure one_out_param_proc

(in_customer_name IN character varying,

 in_address_id IN numeric,

 out_payment_amount OUT numeric)

 Language 'plpgsql'

 AS $BODY$

 

 Declare

 Begin

  Select sum(amount) into out_payment_amount

From payment

where customer_id = (select customer_id from customer

where first_name = in_customer_name

and address_id = in_address_id);

exception

when others then

raise notice 'Something went wrong';

end;

$BODY$

select sum(amount) from payment where customer_id = 341;

select * from customer where customer_id = 341;

do $$

declare

v_payment_amount numeric(10,2);

begin

call one_out_param_proc ('Peter', 346, v_payment_amount);

raise notice 'Total Payment Amount is %',v_payment_amount;

end $$;