How to declare a variable using exact structure of the refcursor?

Question

My database is PostgreSQL 9.3.5.

I have a dynamic query which will be populated with a different number of columns for each run. For that we have to capture that runtime structure. we thought refcursor structure is okay. But how to declare?

Example:

DECLARE
  curs1 refcursor;
  v_cols  curs1%ROWTYPE;----> It is giving us error
BEGIN
  OPEN curs1 FOR EXECUTE 'SELECT * FROM '|| tablename||'';
  LOOP
    FETCH curs1 INTO ....;  --->Here how to capture the data
    EXIT WHEN NOT FOUND;
  END LOOP;
  CLOSE curs1;
END;

Always include the function header in questions, it's an integral part of the function. Parameters and return type are relevant for the rest to make sense. The data type of `tablename` for starters. — Erwin Brandstetter, Feb 27 '15 at 19:15

score 0 · Answer 1 · edited May 23 '17 at 11:43

Like @a_horse commented, record will do the job. Be aware that record is just a placeholder for any row type and cannot be accessed before it is assigned to the first time:

Passing column names dynamically for a record variable in PostgreSQL

CREATE OR REPLACE FUNCTION foo(_tbl regclass)
  RETURNS ??? AS
$func$
DECLARE
  v_cols record;
BEGIN
  FOR v_cols IN
     EXECUTE 'SELECT * FROM '|| _tbl;
  LOOP
     -- loop is only entered if a (next) row is found
     -- do stuff
  END LOOP;
END
$func$ LANGUAGE plpgsql;

A FOR loop is typically simpler and faster than handling cursors manually.

For dynamic code use FOR-IN-EXECUTE - and be sure to defend against SQL injection!
regclass as data type for the parameter does that for (existing!) tablenames:

Table name as a PostgreSQL function parameter

How to declare a variable using exact structure of the refcursor?

1 Answers1