Loop over array dimension in plpgsql

Question

In plpgsql, I want to get the array contents one by one from a two dimension array.

DECLARE   m varchar[];   arr varchar[][] := array[['key1','val1'],['key2','val2']]; BEGIN   for m in select arr   LOOP     raise NOTICE '%',m;   END LOOP; END; 

But the above code returns:

{{key1,val1},{key2,val2}} 

in one line. I want to be able to loop over and call another function which takes parameters like:

another_func(key1,val1) 

Answer 1

Since PostgreSQL 9.1 there is the convenient FOREACH:

DO $do$ DECLARE    m   varchar[];    arr varchar[] := array[['key1','val1'],['key2','val2']]; BEGIN    FOREACH m SLICE 1 IN ARRAY arr    LOOP       RAISE NOTICE 'another_func(%,%)',m[1], m[2];    END LOOP; END $do$ 

Solution for older versions:

DO $do$ DECLARE    arr varchar[] := '{{key1,val1},{key2,val2}}'; BEGIN    FOR i IN array_lower(arr, 1) .. array_upper(arr, 1)    LOOP       RAISE NOTICE 'another_func(%,%)',arr[i][1], arr[i][2];    END LOOP; END $do$ 

Also, there is no difference between varchar[] and varchar[][] for the PostgreSQL type system. I explain in more detail here.

The DO statement requires at least PostgreSQL 9.0, and LANGUAGE plpgsql is the default (so you can omit the declaration).