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