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) There is the convenient FOREACH which can loop over slices of arrays. The manual:
The target variable must be an array, and it receives successive slices of the array value, where each slice is of the number of dimensions specified by
SLICE.
DO $do$ DECLARE m text[]; arr text[] := '{{key1,val1},{key2,val2}}'; -- array literal BEGIN FOREACH m SLICE 1 IN ARRAY arr LOOP RAISE NOTICE 'another_func(%,%)', m[1], m[2]; END LOOP; END $do$; db<>fiddle here - with a function printing results, instead of DO
LANGUAGE plpgsql is the default for a DO statement so we can omit the declaration.
There is no difference between text[] and text[][] for the Postgres type system. See:
DO $do$ DECLARE arr text[] := array[['key1','val1'],['key2','val2']]; -- array constructor 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$; 
You can use a FOREACH statement to iterate a 2D array as shown below:
DO $$ DECLARE temp VARCHAR; _2d_arr VARCHAR[] := ARRAY[ ['a','b','c'], ['d','e','f'] ]; BEGIN FOREACH temp SLICE 0 IN ARRAY _2d_arr LOOP RAISE INFO '%', temp; END LOOP; END $$; Or, you can use a FOR statement to iterate a 2D array as shown below:
DO $$ DECLARE _2d_arr VARCHAR[] := ARRAY[ ['a','b','c'], ['d','e','f'] ]; BEGIN FOR num1 IN 1..2 LOOP FOR num2 IN 1..3 LOOP RAISE INFO '%', _2d_arr[num1][num2]; END LOOP; END LOOP; END $$; Then, you can iterate the 2D array as shown below:
INFO: a INFO: b INFO: c INFO: d INFO: e INFO: f DO 
