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; $$;
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; $$;