1

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']
])
  • The argument of your function is defined as as array of character varying. Then, you cannot use an array of arrays. – Dario Apr 5 '18 at 3:47
  • How do you store this arrays? Is there any table? Could you post your table schema? And please tag your postgres version. – McNets Apr 5 '18 at 6:54
  • @McNets I do not store this array at all, it will only be passed to the function, where I need the information total_pop as a column name and sum as the name of an aggregation function. I use plpgsql to build my query string. – Michael Apr 5 '18 at 8:34
  • @Dario I thought the [] make it an array as in this example (however with INT[]) stackoverflow.com/a/571205/1623867 – Michael Apr 5 '18 at 8:40
  • You are using an array as a key/value pair replacement. JSON or hstore would be better suited for that. – a_horse_with_no_name Apr 5 '18 at 9:06
1

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

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.