Re: Calling Procedure from another procedure in Postgres

Muthukumar.GK schrieb am 02.12.2020 um 11:20:
> I need to call the procedure(not function) and insert the records
> into a temporary table from another procedure in postgres. When
> executing the procedure 'Sampleproc2',I got some below syntax error.
> Kindly let me know whether postgres supports this functionality or
> any other way of calling the procedure from another procedure.

> CREATE OR REPLACE PROCEDURE SampleProc1()    
>     as    $$
>     declare
>        c1 refcursor:='result1';     
>     begin
>      open c1 for
>     select approverid,assigntoid,effstdt,effenddtfrom tblApproverreassign;
>     end;
>     $$
>     language plpgsql;
>
>                 INSERT INTO TMPApproverAssign
>                 *CALL SampleProc1();     *

You need to make sampleproc1 a set returning _function_, then you can do:

INSERT INTO TMPApproverAssign
select *
from sampleproc1();

Procedures aren't meant to return stuff, that's what functions are for.