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.?????
Advertisement
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):