CODEWITHSUNDEEP
postgresql
AshishK
AshishK

Reputation: 11

Merge Statement with temp table

CREATE OR REPLACE FUNCTION public.merge_test (
   r_obj refcursor,
   _ldeptid character varying
) RETURNS refcursor
   LANGUAGE 'plpgsql'  COST 100.0  VOLATILE AS $function$
BEGIN
   DROP TABLE IF EXISTS tblCumulate;    
   create temp table tblCumulate (
      lCompid varchar(10),
      lOpenCount int default 0,
      lClosedCount int default 0
   );  
   DROP TABLE IF EXISTS tblOpen;  
   create temp table tblOpen (
      lOSID SERIAL,
      lCount numeric(24,0),
      lCompid varchar(100)
   );  
   MERGE  into  tblCumulate CUM using (select lcompid,lCount from tblopen) as OP   
      on CUM.lcompid=OP.lcompid
      when matched
         then update set cum.lOpenCount=op.lcount  
      when not matched
         then insert (lCompid,lOpenCount) values op.lcompid,op.lcount);
   open r_obj for  
      select * from tblCumulate;  
   return r_obj;  
END;
$function$;

when I execute (Run) this procedure showing following error.

ERROR:  "tblcumulate" is not a known variable
LINE 41:  MERGE  into  tblCumulate   CUM  temp

Upvotes: 1

Views: 1028

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246248

MERGE was introduced in PostgreSQL v15. Before that, you would have to use INSERT ... ON CONFLICT.

Anyway, the problem here is that PL/pgSQL functions cache parsed statements. So tblCumulate is resolved to some internal object ID during the first execution of the function in a database session, and the second execution would not find that object any more.

One thing you could try is to disable the caching of execution plans; perhaps that is enough to solve the problem:

ALTER FUNCTION public.merge_test (refcursor, character varying)
   SET plan_cache_mode = force_custom_plan;

If that is not enough, you'll have to use dynamic SQL:

EXECUTE 'MERGE INTO tblCumulate ...';

Upvotes: 1

Related Questions