Обсуждение: Calling Procedure from another procedure in Postgres

Поиск
Список
Период
Сортировка

Calling Procedure from another procedure in Postgres

От
"Muthukumar.GK"
Дата:
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

------------------------------------------------------------------

 

Re: Calling Procedure from another procedure in Postgres

От
Hemil Ruparel
Дата:
insert into <table> is not valid

On Wed, Dec 2, 2020 at 3:50 PM Muthukumar.GK <muthankumar@gmail.com> wrote:
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

------------------------------------------------------------------

 

Re: Calling Procedure from another procedure in Postgres

От
Pavel Stehule
Дата:


st 2. 12. 2020 v 11:20 odesílatel Muthukumar.GK <muthankumar@gmail.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

 

Re: Calling Procedure from another procedure in Postgres

От
Thomas Kellerer
Дата:
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.