How To Create And Call A Stored Procedure With Multiple/Many OUT Paramet...

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 public.two_out_param_proc(

IN in_customer_name character varying,

IN in_address_id numeric,

OUT out_payment_amount numeric,

OUT out_max_payment_date date

)

LANGUAGE 'plpgsql'

AS $BODY$

 

 Declare

 Begin

  Select sum(amount), max(payment_date) 

into out_payment_amount, out_max_payment_date

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

ALTER PROCEDURE public.two_out_param_proc(character varying, numeric, numeric, date)

    OWNER TO postgres;

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

select * from customer where customer_id = 341;

do $$

declare

v_payment_amount numeric(10,2);

v_max_payment_date date;

begin

call two_out_param_proc('Peter',346,v_payment_amount,v_max_payment_date);

raise notice 'The total payment amount is % and the latest payment date is %',v_payment_amount,v_max_payment_date;

end $$;