Postgres 9.1 Type of SETOF record

postgresql, record, types

Solution

You could cast the value to text inside the function, and declare that the function `RETURNS SETOF text`. You can also return the whole result set at once; no need to iterate explicitly.

CREATE TABLE dates (column1 int, column2 date);
INSERT INTO dates VALUES (1, date '2012-12-22'), (2, date '2013-01-01');

CREATE FUNCTION test(column_name text) RETURNS SETOF text AS $$
BEGIN
   RETURN QUERY EXECUTE 'SELECT '
      || quote_ident(column_name) || '::text FROM dates';
END;
$$ LANGUAGE 'plpgsql';

Now `SELECT test('column1');` yields:

 test 
------
 1
 2
(2 rows)

... and (with my locale settings) `SELECT test('column2');` yields:

    test    
------------
 2012-12-22
 2013-01-01
(2 rows)

Problem

I have dynamicly generated SELECT. I try to return result as SETOF RECORD. Sth like that: ``` CREATE FUNCTION test(column_name text) RETURNS SETOF RECORD AS $$ DECLARE row RECORD; BEGIN FOR row IN EXECUTE 'SELECT ' || quote_ident(column_name) || ' FROM dates' LOOP RETURN NEXT row; END LOOP; RETURN; END; $$ LANGUAGE 'plpgsql'; ``` When I try: ``` SELECT * FROM test('column1'); ``` I get this: ``` ERROR: a column definition list is required for functions returning "record" ``` I know that column1 is integer type: ``` SELECT * FROM test('column1') f(a int); ``` result is correct, because I know that this is going to be Integer type. When I try: ``` SELECT * FROM test('column1') f(a varchar); ``` I get error: ``` ERROR: wrong record type supplied in RETURN NEXT DETAIL: Returned type integer does not match expected type character varying in column 1. ``` Now my question: What to do to get rid of part of querty where I define types 'f(a int)'. It should by feasible because Postgres knowns what is returned type. I tried with IMMUTABLE options, but unsuccessfully.

Original source