PostgreSQL – “INSERT INTO … FETCH All from …” unable to compile

CREATE OR REPLACE FUNCTION public.test_returning_cursor( )
 RETURNS refcursor
IMMUTABLE
LANGUAGE plpgsql
AS $$
DECLARE
 _ref refcursor ='test_returning_cursor_ref1';
BEGIN
 OPEN _ref FOR
 SELECT'a' :: text AS col1
 UNION
 SELECT'b'
 UNION
 SELECT'c';

 RETURN _ref;
END
$$;

CREATE OR REPLACE FUNCTION public.test_insert_from_cursor()
 RETURNS table(col1 text)
IMMUTABLE
LANGUAGE plpgsql
AS $$
BEGIN
 CREATE TEMP TABLE _temptable (
 col1 text
) ON COMMIT DROP;< br />
 INSERT INTO _temptable (col1)
 FETCH ALL FROM "test_returning_cursor_ref1";

 RETURN QUERY
 SELECT col1
 FROM _temptable;
END
$$; 

FOR _rec IN
 FETCH ALL FROM "test_returning_cursor_ref1"
LOOP
 INSERT INTO ...
END LOOP;

CREATE OR REPLACE FUNCTION f_cursor1_to_tbl()
 RETURNS TABLE (col1 text) AS
$func$
BEGIN
 - MOVE BACKWARD ALL FROM test_returning_cursor_ref1; - optional, see below

 RETURN QUERY
 FETCH ALL FROM test_returning_cursor_ref1;
END
$func$ LANGUAGE plpgsql; - not IMMUTABLE

CREATE TEMP TABLE t1 ON COMMIT DROP
AS SELECT * FROM f_cursor1_to_tbl();

I have some functions to return cursors (refcursor) on PostgreSQL 9.6:

CREATE OR REPLACE FUNCTION public.test_returning_cursor()
 RETURNS refcursor
IMMUTABLE
LANGUAGE plpgsql
AS $$
DECLARE
 _ref refcursor ='test_returning_cursor_ref1';
BEGIN
 OPEN _ref FOR
 SELECT'a' :: text AS col1
 UNION
 SELECT'b'
 UNION
 SELECT'c';

 RETURN _ref;< br />END
$$;

I need to write another function in which I create a temporary table and insert all data from this refcursor into it. But INSERT INTO… FETCH ALL FROM… seems impossible. Such a function cannot be compiled:

CREATE OR REPLACE FUNCTION public.test_insert_from_cursor()
 RETURNS table(col1 text)
IMMUTABLE
LANGUAGE plpgsql
AS $$
BEGIN
 CREATE TEMP TABLE _temptable (
 col1 text
) ON COMMIT DROP;

 INSERT INTO _temptable (col1)
 FETCH ALL FROM "test_returning_cursor_ref1";

 RETURN QUERY
 SELECT col1
 FROM _temptable;
END
$$;

I know I can use:

FOR _rec IN
 FETCH ALL FROM "test_returning_cursor_ref1"
LOOP< br /> INSERT INTO ...
END LOOP;

But is there a better way?

Unfortunately, INSERT and SELECT cannot access the entire cursor.

To avoid expensive single-row INSERT, you can use The intermediate function of RETURNS TABLE, and use RETURN QUERY to return the cursor as a table. See:

> Return a query from a function?

CREATE OR REPLACE FUNCTION f_cursor1_to_tbl()
 RETURNS TABLE (col1 text) AS
$func$
BEGIN
 - MOVE BACKWARD ALL FROM test_returning_cursor_ref1; - optional, see below

 RETURN QUERY< br /> FETCH ALL FROM test_returning_cursor_ref1;
END
$func$ LANGUAGE plpgsql; - not IMMUTABLE

Then create a temporary table directly, such as:

< /p>

CREATE TEMP TABLE t1 ON COMMIT DROP
AS SELECT * FROM f_cursor1_to_tbl();

See:

> Creating temporary tables in SQL

Still not very elegant, but much faster than single-row INSERT.

Note: Since the source is a cursor, only the first call succeeds. The second execution of the function will return an empty Set. You need a cursor with the SCROLL option, and then move to the beginning of the repeated call.