Re: Calling Procedure from another procedure in Postgres

st 2. 12. 2020 v 11:20 odesílatel Muthukumar.GK <muthankumar(at)gmail(dot)com>
napsal:

> Hi team,
>
> 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;
>
>
> -------------------------------------------------------------------------------------------------------------
>
> CREATE OR REPLACE PROCEDURE SampleProc2()
>
> as $$
>
> declare c1 refcursor:='result1';
>
> begin
>
> CREATE TEMP TABLE TMPApproverAssign
>
> ( approverid VARCHAR(10),
>
> assigntoid VARCHAR(10),
>
> effstdt timestamptz,
>
> effenddt timestamptz
>
> ) ON COMMIT DROP;
>
>
>
> INSERT INTO TMPApproverAssign
>
> *CALL SampleProc1(); *
>
>
> open c1 for
>
> select approverid,assigntoid,effstdt,effenddtfrom TMPApproverAssign;
>
> end;
>
> $$
>
> language plpgsql;
>
>
> ---------------------------------------------------------------------------------------------
>
> Error : syntax error at or near "CALL"
>
> LINE 12 : ^CALL SampleProc1();
>
> SQL state : 42601
>
> Character:453
>
> ------------------------------------------------------------------
>

Procedures in Postgres cannot to returns tables, so INSERT INTO CALL is
unsupported

Regards

Pavel

>