How to iterate over a record when the columns are dynamic

Question

I have this function in postgres which takes PVH_COLS_DYNA that contains the columns that are going in to the query:

CREATE OR REPLACE FUNCTION DRYNAMIC_DATA_F(PVH_COLS_DYNA VARCHAR) RETURNS numeric AS $$
    DECLARE

      VV_QUERY_DINAMIC VARCHAR;
      VV_ROW_RECORD record;

    BEGIN
                        
    VV_QUERY_DINAMIC:=' SELECT '|| PVH_COLS_DYNA ||' FROM as_detalle_carga WHERE fk_id_carga_cartera = 1234 ;';


    FOR VV_ROW_RECORD IN EXECUTE VV_QUERY_DINAMIC LOOP
            raise notice ' data  % ', VV_ROW_RECORD.???????; 
    END LOOP;

    return 1;
    
    END;
    $$ LANGUAGE plpgsql;

How can I get the data from the record variable VV_ROW_RECORD, since the columns are dynamic?

    VV_ROW_RECORD.1
    VV_ROW_RECORD.?1
    VV_ROW_RECORD.[1]
    VV_ROW_RECORD.?????

score 0 · Accepted Answer · edited May 23 '17 at 12:13

You cannot reference columns like array items, columns have to be referenced by name.

The dynamic part is not getting the row in your example, but referencing each column.

CREATE OR REPLACE FUNCTION dynamic_data_f(pvh_cols_dyna text)
  RETURNS numeric AS
$func$
DECLARE
   _row  as_detalle_carga%ROWTYPE;
   _col  text;
   _data text;
BEGIN

SELECT *
INTO   _row 
FROM   as_detalle_carga
WHERE  fk_id_carga_cartera = 1234;

FOREACH _col IN ARRAY string_to_array(pvh_cols_dyna, ',') 
LOOP
   EXECUTE format('SELECT ($1).%I::text', trim(_col))
   USING   _row
   INTO    _data;

   RAISE NOTICE 'data: % ', _data;
END LOOP;

RETURN 1;

END
$func$ LANGUAGE plpgsql;

%I is an argument to format(), properly escaping identifiers as needed.
$1 in the query string for EXECUTE is a parameter filled in by the USING clause (not to be confused with function parameters!).

Related answers (with more explanation):

Great it works just fine with your example (i added a **for** to the first select to suit my needs), sorry can u explain a little about what `format('SELECT ($1).%I::text', trim(_col))` does and especially **$1** and **%I**. Thanks again!!! — PaulMB, Aug 25 '14 at 14:42
@PaulJava: I added more explanation and links. Be sure to visit the related answers with more explanation. — Erwin Brandstetter, Aug 25 '14 at 15:10
Thanks a lot Mr Erwin, ill be reading the related answers, thanks for adding more explanation!! been looking for answers for over 2 weeks now :) thanks! — PaulMB, Aug 25 '14 at 15:18

pozs · Answer 2 · 2014-08-25T14:51:26.500

You cannot iterate record columns directly. You have to convert it first into something iterable, like json or hstore.

FOR vv_row_record IN EXECUTE vv_query_dynamic LOOP

    FOR vv_row_record_pairs IN SELECT * FROM json_each(row_to_json(vv_row_record)) LOOP
        RAISE NOTICE ' field "%" in json is % ',
            vv_row_record_pairs.key,
            vv_row_record_pairs.value; 
    END LOOP;

    -- OR

    FOR vv_row_record_pairs IN SELECT * FROM each(hstore(vv_row_record)) LOOP
        RAISE NOTICE ' field "%" in text representation is % ',
            vv_row_record_pairs.key,
            vv_row_record_pairs.value; 
    END LOOP;

END LOOP;

i dont know much about json but i read something about hstore im gonna give it a try and investigate, thanks for repliying! — PaulMB, Aug 25 '14 at 14:43

How to iterate over a record when the columns are dynamic

2 Answers2