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 
declare strSQL text := '';
declare _field text := '';
    foreach _field in array _fields[:][1:2]
            strSQL :=  'fieldname:' || _field[1] || ' stats: ' || _field[2] ;
            raise notice '% EXECUTING %', to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), strSQL;
        end loop;
$func$ language plpgsql;

I want to call the function with:

select public.whatwhatwhy(ARRAY[
['total_pop', 'sum'],

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]"


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'],
  • The argument of your function is defined as as array of character varying. Then, you cannot use an array of arrays.
    – Dario
    Apr 5, 2018 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, 2018 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, 2018 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, 2018 at 8:40
  • You are using an array as a key/value pair replacement. JSON or hstore would be better suited for that.
    – user1822
    Apr 5, 2018 at 9:06

2 Answers 2


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
   m text[];
      RAISE NOTICE '% EXECUTING fieldname: %, stats: %', now()::text, m[1], m[2];
$func$  LANGUAGE plpgsql;

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



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 
declare strSQL text := '';
    for i in 1 .. array_upper(_fields,1)
            strSQL :=  'fieldname:' || _fields[i][1] || ' stats: ' || _fields[i][2] ;
            raise notice '% EXECUTING %', to_char(now(), 'YYYY-MM-DD HH24:MI:SS'), strSQL;
        end loop;
$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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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