Loop through slices of 2-dimensional array in plpgsql function?

Question

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[1] || ' stats: ' || _field[2] ;
            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']
])

Question 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 [42804]: ERROR: cannot subscript type text because it is not an array
Where: SQL statement "SELECT 'fieldname:' || _field[1] || ' stats: ' || _field[2]"

Note

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']
])

Answer 1

A FOR loop over array_upper(_fields,1) like you found is a simple and good option.

Even simpler for your case (for Postgres 9.1 or later): FOREACH with SLICE. The manual:

With a positive SLICE value, FOREACH iterates through slices of the array rather than single elements.

Your function could look like this:

CREATE OR REPLACE FUNCTION public.mj_rubbish(_fields text[])
   RETURNS void AS
$func$
DECLARE
   m text[];
BEGIN
   FOREACH m SLICE 1 IN ARRAY _fields
   LOOP
      RAISE NOTICE '% EXECUTING fieldname: %, stats: %', now()::text, m[1], m[2];
   END LOOP;
END
$func$  LANGUAGE plpgsql;

This also works regardless of actual array indices - which do no have to start with 1 (but do by default).

Related:

• Loop over array dimension in plpgsql
• Step through multiple arrays in parallel with FOREACH
• Normalize array subscripts for 1-dimensional array so they start with 1

Answer 2

Thanks to the commenters.

This SE answer helped me at the end to solve the problem.

• Declaring _field is not necessary.
• The foreach loop becomes a simple loop.
• Each field is addressed by [i][1] for the column name e.g. total_pop and [i][2] for the statistics e.g. sum.

The function has to be like this:

create  or replace function public.mj_rubbish(
        _fields character varying []
    ) returns void as 
$func$
declare strSQL text := '';
begin 
    for i in 1 .. array_upper(_fields,1)
        loop 
            strSQL :=  'fieldname:' || _fields[i][1] || ' stats: ' || _fields[i][2] ;
            raise notice '% EXECUTING %', to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), strSQL;
        end loop;
end
$func$ language plpgsql;

Will output:

00000: 2018-04-06 12:08:53 EXECUTING fieldname:total_pop stats: sum
00000: 2018-04-06 12:08:53 EXECUTING fieldname:total_male_pop stats: sum
00000: 2018-04-06 12:08:53 EXECUTING fieldname:median_male_age stats: median
00000: 2018-04-06 12:08:53 EXECUTING fieldname:median_female_age stats: median