Re: Calling Procedure from another procedure in Postgres

[Thomas Kellerer <shammat@xxxxxxx>]

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.