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.