CODEWITHSUNDEEP
databasepostgresqlfor-loopmultidimensional-arrayplpgsql
blue01
blue01

Reputation: 2095

Loop over array dimension in plpgsql

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)

Upvotes: 71

Views: 166550

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658717

Since PostgreSQL 9.1

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:

Postgres 9.0 or older

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$;

Upvotes: 150

Related Questions