How to iterate over a record when the columns are dynamic

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

Answer

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):

• Postgres pl/pgsql ERROR: column “column_name” does not exist

• Iterating over integer[] in PL/pgSQL

• How to use EXECUTE FORMAT … USING in postgres function