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;

Solution 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.

Solution 2

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.

Solution 3

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

Comments

[Erwin Brandstetter]

There are solutions .. depending on the data types in use. Can you please add a typical table definition and some example data, the desired form of output and the complete function definition including parameters? Is the function intended for one table or for different tables? The latter requires you to hand in a table name via parameter and use dynamic SQL ...

[Craig Ringer]

regress=# SELECT unnest(ROW(1,2,'test')); ERROR: function unnest(record) does not exist

[Clodoaldo Neto]

@Craig Yes he would have to pick data1.myArray. At least I suppose it is an array as he is trying to unnest it

[Craig Ringer]

I think he wants to unnest a record into key/value pairs. Which simply isn't supported - and doesn't make sense in a typed relational model unless you do like hstore does and convert all values to text anyway.

[RKI]

Thanks! It's looks cool, but when I don't "know" table structure, this not for me.

[RKI]

Thanks! But you don't understand my question.

[RKI]

Thanks! But you don't understand my question.

[RKI]

Thanks a lot! I thinking about this way.

[Erwin Brandstetter]

@RomanKutsy: I think you misunderstand. The final solutions is for just that case. Try and read again.

[RKI]

Thanks! Now I understood. I will try your solution in few days.

[markop]

I guess this is what Erwin is refering to create or replace function row_to_jsonarray(r anyelement) returns json language sql immutable AS $$ select to_json(array (select value from each(hstore(r)) ) ); $$ ; Works in PostgreSQL 9.4. But mind that hstore does not guarantee the order of the columns in the record to be the same in the resulting json.