Loop through columns of RECORD

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 tWHERE  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'::regclassAND    a.attnum > 0AND    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$BEGINRETURN 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-----11text2012-10-0122text2012-10-023,3,ex,2012-10-034",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.

PL/pgSQL isn't really designed for what you want to do. It doesn't consider a record to be iterable, it's a tuple of possibly different and incompatible data types.

PL/pgSQL has EXECUTE for dynamic SQL, but EXECUTE queries cannot refer to PL/pgSQL variables like NEW or other records directly.

What you can do is convert the record to a hstore key/value structure, then iterate over the hstore. Use each(hstore(the_record)), which produces a rowset of key,value tuples. All values are cast to their text representations.

This toy function demonstrates iteration over a record by creating an anonymous ROW(..) - which will have column names f1, f2, f3 - then converting that to hstore, iterating over its column/value pairs, and returning each pair.

CREATE EXTENSION hstore;CREATE OR REPLACE FUNCTION hs_demo()RETURNS TABLE ("key" text, "value" text)LANGUAGE plpgsql AS$$DECLARE  data1 record;  hs_row record;BEGIN  data1 = ROW(1, 2, 'test');  FOR hs_row IN SELECT kv."key", kv."value" FROM each(hstore(data1)) kv  LOOP    "key" = hs_row."key";    "value" = hs_row."value";    RETURN NEXT;  END LOOP;END;$$;

In reality you would never write it this way, since the whole loop can be replaced with a simple RETURN QUERY statement and it does the same thing each(hstore) does anyway - so this is only to show how each(hstore(record)) works, and the above function should never actually be used.

This feature is not supported in plpgsql - Record IS NOT hash array like other scripting languages - it is similar to C or ADA, where this functionality is impossible. You can use other PL language like PLPerl or PLPython or some tricks - you can iterate with HSTORE datatype (extension) or via dynamic SQL

see How to set value of composite variable field using dynamic SQL

But request for this functionality usually means, so you do some wrong. When you use PL/pgSQL you have think different than you use Javascript or Python