How to loop through an array of arrays in a function and access its elements?
Below is the the function (this is only the relevant part).
create or replace function public.whatwhatwhy( _fields character varying  ) returns void as $func$ declare strSQL text := ''; declare _field text := ''; begin foreach _field in array _fields[:][1:2] loop strSQL := 'fieldname:' || _field || ' stats: ' || _field ; raise notice '% EXECUTING %', to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), strSQL; end loop; end $func$ language plpgsql;
I want to call the function with:
select public.whatwhatwhy(ARRAY[ ['total_pop', 'sum'], ['total_male_pop','sum'], ['median_female_age','median'] ])
How do I access e.g. total_pop and sum in my
_field? I assumed the
_field variable holds another array however I get an error:
SQL Error : ERROR: cannot subscript type text because it is not an array
Where: SQL statement "SELECT 'fieldname:' || _field || ' stats: ' || _field"
I tried this statement, hoping that this solves the problem, but it seems to be identical to the statement above.
select public.whatwhatwhy(ARRAY[ ARRAY['total_pop', 'sum'], ARRAY['total_male_pop','sum'], ARRAY['median_female_age','median'] ])