postgresql - Loop through columns of RECORD

Question

I need to loop through type RECORD items by key/index, like I can do this using array structures in other programming languages.

For example:

DECLARE
    data1    record;
    data2    text;
...
BEGIN
...
FOR data1 IN
    SELECT
        *
    FROM
        sometable
LOOP

    FOR data2 IN
        SELECT
            unnest( data1 )   -- THIS IS DOESN'T WORK!
    LOOP
        RETURN NEXT data1[data2];   -- SMTH LIKE THIS
    END LOOP;

END LOOP;

Answer 1

As @Pavel explained, it is not simply possible to traverse a record, like you could traverse an array. But there are several ways around it - depending on your exact requirements. Ultimately, since you want to return all values in the same column, you need to cast them to the same type - text is the obvious common ground, because there is a text representation for every type.

Quick and dirty

Say, you have a table with an integer, a text and a date column.

CREATE TEMP TABLE tbl(a int, b text, c date);
INSERT INTO tbl VALUES
 (1, '1text',     '2012-10-01')
,(2, '2text',     '2012-10-02')
,(3, ',3,ex,',    '2012-10-03')  -- text with commas
,(4, '",4,"ex,"', '2012-10-04')  -- text with commas and double quotes

Then the solution can be a simple as:

SELECT unnest(string_to_array(trim(t::text, '()'), ','))
FROM   tbl t;

Works for the first two rows, but fails for the special cases of row 3 and 4.
You can easily solve the problem with commas in the text representation:

SELECT unnest(('{' || trim(t::text, '()') || '}')::text[])
FROM   tbl t
WHERE  a < 4;

This would work fine - except for line 4 which has double quotes in the text representation. Those are escaped by doubling them up. But the array constructor would need them escaped by . Not sure why this incompatibility is there ...

SELECT ('{' || trim(t::text, '()') || '}') FROM tbl t WHERE a = 4

Yields:

{4,""",4,""ex,""",2012-10-04}

But you would need:

SELECT '{4,"",4,"ex,"",2012-10-04}'::text[];  -- works

Proper solution

If you knew the column names beforehand, a clean solution would be simple:

SELECT unnest(ARRAY[a::text,b::text,c::text])
FROM tbl

Since you operate on records of well know type you can just query the system catalog:

SELECT string_agg(a.attname || '::text', ',' ORDER  BY a.attnum)
FROM   pg_catalog.pg_attribute a 
WHERE  a.attrelid = 'tbl'::regclass
AND    a.attnum > 0
AND    a.attisdropped = FALSE

Put this in a function with dynamic SQL:

CREATE OR REPLACE FUNCTION unnest_table(_tbl text)
  RETURNS SETOF text LANGUAGE plpgsql AS
$func$
BEGIN

RETURN QUERY EXECUTE '
SELECT unnest(ARRAY[' || (
    SELECT string_agg(a.attname || '::text', ',' ORDER  BY a.attnum)
    FROM   pg_catalog.pg_attribute a 
    WHERE  a.attrelid = _tbl::regclass
    AND    a.attnum > 0
    AND    a.attisdropped = false
    ) || '])
FROM   ' || _tbl::regclass;

END
$func$;

Call:

SELECT unnest_table('tbl') AS val

Returns:

val
-----
1
1text
2012-10-01
2
2text
2012-10-02
3
,3,ex,
2012-10-03
4
",4,"ex,"
2012-10-04

This works without installing additional modules. Another option is to install the hstore extension and use it like @Craig demonstrates.