"INSERT INTO ... FETCH ALL FROM ..." can't be compiled

Question

I have some function on PostgreSQL 9.6 returning a cursor (refcursor):

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

I need to write another function in which a temp table is created and all data from this refcursor are inserted to it. But INSERT INTO ... FETCH ALL FROM ... seems to be impossible. Such function can't 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 that I can use:

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

But is there better way?

It would be a lot easier if the first function was defined as `returns table (...)` as well — , Jun 13 '18 at 12:49
The first function must return refcursor by task specification. — Evgeny Nozdrev, Jun 13 '18 at 13:20
Aside: Use `UNION ALL` or a `VALUES` expression instead of `UNION` in the 1st query. But the whole approach is flawed. What kind of task specification would demand an inadequate technique? — Erwin Brandstetter, Jun 13 '18 at 16:30
@ErwinBrandstetter we are rewriting database from MS SQL to Postgre. MS SQL has procedures, so 1 procedure can return multiple tables. Postgre can't, so we decided to return setof refcursor, 1 refcursor for 1 table. That's why first function _must_ return refcursor (or setof refcursor), not table(). But I do not know an adequate way to insert data from refcursor to temp table. — Evgeny Nozdrev, Jun 14 '18 at 07:54
I see where you are coming from. The new [SQL procedures in Postgres 11](https://www.postgresql.org/docs/devel/static/sql-createprocedure.html) are designed to also return multiple results eventually, but that's not implemented in pg 11, yet. — Erwin Brandstetter, Jun 14 '18 at 10:34

Erwin Brandstetter · Accepted Answer · 2018-06-14T14:47:08.420

1

Unfortunately, INSERT and SELECT don't have access to cursors as a whole.

To avoid expensive single-row INSERT, you could have intermediary functions with RETURNS TABLE and return the cursor as table with RETURN QUERY. 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
   FETCH ALL FROM test_returning_cursor_ref1;
END
$func$  LANGUAGE plpgsql;  -- not IMMUTABLE

Then create the temporary table(s) directly like:

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. Executing the function a second time would return an empty set. You would need a cursor with the SCROLL option and move to the start for repeated calls.

edited Jun 14 '18 at 14:47

answered Jun 14 '18 at 10:31

Erwin Brandstetter

605,456
145
1,078
1,228

But how can I write such intermediary function? It must read from cursor and return table, so it will have SELECT...FROM FETCH ALL... , but such construction is not allowed. I know only 1 workaround, but that way is awful. – Evgeny Nozdrev Jun 14 '18 at 11:01
1

Thanks, I wrote intermediary function with "RETURN QUERY FETCH ALL FROM ..." to convert cursor's data to the table and it worked. – Evgeny Nozdrev Jun 14 '18 at 11:07
@ЕвгенийНоздрев: Yes. And simplify your temp table creation. I added some more above. – Erwin Brandstetter Jun 14 '18 at 14:38

score 0 · Answer 2 · answered Oct 19 '18 at 09:17

This function does INSERT INTO from refcursor. It is universal for all the tables. The only requirement is that all columns of table corresponds to columns of refcursor by types and order (not necessary by names).

to_json() does the trick to convert any primitive data types to string with double-quotes "", which are later replaced with ''.

CREATE OR REPLACE FUNCTION public.insert_into_from_refcursor(_table_name text, _ref refcursor)
  RETURNS void
LANGUAGE plpgsql
AS $$
DECLARE
  _sql       text;
  _sql_val   text = '';
  _row       record;
  _hasvalues boolean = FALSE;
BEGIN

  LOOP   --for each row
    FETCH _ref INTO _row;
    EXIT WHEN NOT found;   --there are no rows more

    _hasvalues = TRUE;

    SELECT _sql_val || '
           (' ||
           STRING_AGG(val.value :: text, ',') ||
           '),'
        INTO _sql_val
    FROM JSON_EACH(TO_JSON(_row)) val;
  END LOOP;

  _sql_val = REPLACE(_sql_val, '"', '''');
  _sql_val = TRIM(TRAILING ',' FROM _sql_val);

  _sql = '
          INSERT INTO ' || _table_name || '
          VALUES ' || _sql_val;
  --RAISE NOTICE 'insert_into_from_refcursor(): SQL is: %', _sql;
  IF _hasvalues THEN    --to avoid error when trying to insert 0 values
    EXECUTE (_sql);
  END IF;
END;
$$;

Usage:

CREATE TABLE public.table1 (...);
PERFORM my_func_opening_refcursor();
PERFORM public.insert_into_from_refcursor('public.table1', 'name_of_refcursor_portal'::refcursor);

where my_func_opening_refcursor() contains

DECLARE
  _ref refcursor = 'name_of_refcursor_portal';

OPEN _ref FOR
SELECT ...;

"INSERT INTO ... FETCH ALL FROM ..." can't be compiled

2 Answers2