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;

Hightower

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 ... – Casals

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.

Casals

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

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

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

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

Stockton

Thanks a lot! I thinking about this way. – Hightower

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. – Historicism

Answer 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

Imbed

Answer 4

FOR data2 IN
    SELECT d
    from  unnest( data1 ) s(d)
LOOP
    RETURN NEXT data2;
END LOOP;

Lilililia

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

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

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. – Stockton

Thanks! But you don't understand my question. – Hightower

Answer 5

If you order your results prior to looping, will you accomplish what you want.

for rc in select * from t1 order by t1.key asc loop
 return next rc;
end loop;

will do exactly what you need. It is also the fastest way to perform that kind of task.

Merritt

Thanks! But you don't understand my question. – Hightower

Answer 6

I wasn't able to find a proper way to loop over record, so what I did is converted record to json first and looped over json

declare
    _src_schema varchar := 'db_utility';
    _targetjson json;
    _key   text;
    _value text;
BEGIN
    select row_to_json(c.*) from information_schema.columns c where c.table_name = prm_table and c.column_name = prm_column
        and c.table_schema = _src_schema into _targetjson;
        raise notice '_targetjson %', _targetjson;

        FOR _key, _value IN
        SELECT * FROM jsonb_each_text(_targetjson)
        LOOP
        -- do some math operation on its corresponding value
            RAISE NOTICE '%: %', _key, _value;
        END LOOP;
    return true;
end;

Coracle

Answer 7

It's about over 10 years and there's still no chance to loop over record columns dynamically. But I've got a solution, that helps a little. The following function will create and return record definitions in a json object.

CREATE OR REPLACE FUNCTION transactions.f_get_record_definitions(p_txt_sql text)
 RETURNS json
 LANGUAGE plpgsql
AS $function$
declare
    j_rec_defs json;
    rec record;
    rec2 record;
    txt_type text;
    txt_j_build text;
begin
    execute p_txt_sql limit 1 into rec;
    txt_j_build := '{';
    j_rec_defs := row_to_json(rec)::json;
    for rec2 in
        select * from json_each(j_rec_defs)
    loop
        case json_typeof(rec2.value::json)
        when 'object' then txt_type := 'json';
        when 'string' then txt_type := 'text';
        when 'array' then txt_type := 'array';
        when 'boolean' then txt_type := 'bool';
        when 'number' then txt_type := 'numeric';
        else
            txt_type := 'json';
        end case;
        txt_j_build := txt_j_build||'"'||rec2.key||'":"'||txt_type||'",';
    end loop;
    txt_j_build := left(txt_j_build, -1)||'}';
    return txt_j_build::json;
end;
$function$
;

Well, you can only use the types of json or jsonb, but this is ok in many cases. If someone knows, how to obtain the types directly from rec with pg_type, this would be great.

I really don't know, if this solves the initial problem here, but with this function, you'll be able to create typelists to dynamical return record or setof record in other functions. If you don't like the definitions, you can also do a simple "return next rec2" in the loop instead of determine the types.

Encasement