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)
This question is related to
arrays
postgresql
multidimensional-array
for-loop
plpgsql
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).
Source: Stackoverflow.com