sqlpostgresqlplpgsqlref-cursor

Fetch refcursor into temporary table


I need fetch refcursor into temporary table. Each refcursor column should match appropriate table column + one key (enumerate) column should be in temp table. For example refcursor return below data:

'one' 'Monday'
'two' 'Friday'

And the data which should store in table:

 1 'one' 'Monday'
 2 'two' 'Friday'  

This refcursor is opened in other functions. So I does not know what columns should be in result set. How I can implement something like FETCH ALL curs INTO temp_table ?

I wrote below function but it throws the error for (V_CURS_Rec).*

CREATE OR REPLACE FUNCTION FN_TEST() 
RETURNS VOID LANGUAGE plpgsql 
   AS $$ 
   DECLARE 
   V_CURS REFCURSOR; 
   V_CURS_Rec RECORD; 
   ITER INTEGER; 
BEGIN 
   create temporary table if not exists TMP_TBL 
   ( 
  INDX INTEGER NOT NULL, 
  CNAME VARCHAR(20), 
  CDAY VARCHAR(20), 
   ); 
   DELETE FROM TMP_TBL; 
  SELECT * FROM FN_RET_REFCURSOR() INTO V_CURS; 
  ITER := 1; 
   LOOP 
     FETCH V_CURS INTO V_CURS_Rec; 
     EXIT WHEN NOT FOUND; 
     INSERT INTO TMP_TBL SELECT ITER, (V_CURS_Rec).*; 
    ITER := ITER + 1; 
   END LOOP; 
  RETURN; 
END; $$; 

Solution

  • As a workaround I have done below

    CREATE OR REPLACE FUNCTION FN_TEST() 
    RETURNS VOID LANGUAGE plpgsql 
       AS $$ 
       DECLARE 
       V_CURS REFCURSOR; 
       V_Rec_CNAME VARCHAR(20); 
       V_Rec_CDAY VARCHAR(20); 
       ITER INTEGER; 
    BEGIN 
       create temporary table if not exists TMP_TBL 
       ( 
      INDX INTEGER NOT NULL, 
      CNAME VARCHAR(20), 
      CDAY VARCHAR(20)
       ); 
       DELETE FROM TMP_TBL; 
      SELECT * FROM FN_RET_REFCURSOR() INTO V_CURS; 
      ITER := 1; 
       LOOP 
         FETCH V_CURS INTO V_Rec_CNAME, V_Rec_CDAY; 
         EXIT WHEN NOT FOUND; 
         INSERT INTO TMP_TBL VALUES (ITER, V_Rec_CNAME, V_Rec_CDAY); 
         ITER := ITER + 1; 
       END LOOP; 
      RETURN; 
    END; $$;