Loop through columns of RECORD

by

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.

More Related Contents:

  1. Postgres FOR LOOP
  2. Function to loop through and select data from multiple tables
  3. Cursor based records in PostgreSQL
  4. Loop on tables with PL/pgSQL in Postgres 9.0+
  5. What are ‘$$’ used for in PL/pgSQL
  6. INSERT with dynamic table name in trigger function
  7. The forgotten assignment operator “=” and the commonplace “:=”
  8. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  9. How to set value of composite variable field using dynamic SQL
  10. Loop over array dimension in plpgsql
  11. How do I do large non-blocking updates in PostgreSQL?
  12. Execute a dynamic crosstab query
  13. Is there a way to disable function overloading in Postgres
  14. Can I make a plpgsql function return an integer without using a variable?
  15. PostgreSQL function returning multiple result sets
  16. Dynamic SQL (EXECUTE) as condition for IF statement
  17. Returning from a function with OUT parameter
  18. SELECT raises exception in PL/pgSQL function
  19. Test for null in function with varying parameters
  20. Return SETOF rows from PostgreSQL function
  21. Error when setting n_distinct using a plpgsql variable
  22. Dynamically generate columns for crosstab in PostgreSQL
  23. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  24. Iterating over integer[] in PL/pgSQL
  25. ERROR: unterminated quoted string at or near
  26. How can I send some http request from postgresql function or trigger
  27. Select from a table variable
  28. Hashing a String to a Numeric Value in PostgreSQL
  29. plpgsql error “RETURN NEXT cannot have a parameter in function with OUT parameters” in table-returning function
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment