FOREACH and FOR statements with arrays in PostgreSQL

Buy Me a Coffee☕

A FOREACH statement:

• can repeat a LOOP statement as long as there are array values.

• can be used only with a PL/pgSQL function and procedure and DO statement.

Now, you can create the DO statement which has a FOREACH statement with a LOOP statement to iterate a 1D(one-dimensional) array as shown below:

DO $$
DECLARE
  temp VARCHAR;
  _1d_arr VARCHAR[] := ARRAY['a','b','c'];
BEGIN
  FOREACH temp SLICE 0 IN ARRAY _1d_arr LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

*Memos:

• SLICE 0 can iterate zero or more elements.

• SLICE 1, SLICE 2, SLICE 3, etc can iterate zero or more arrays.

• For a 1D array, you can set SLICE 0 or SLICE 1 so if you set SLICE 2, there is error.

• You can omit SLICE 0, but the way of the iteration is same as SLICE 0.

• The type of temp with SLICE 0 must not be array (VARCHAR[]) otherwise there is error.

• You can set VARCHAR[][], VARCHAR[][][], etc to _1d_arr, then the type of _1d_arr is automatically converted to VARCHAR[](CHARACTER VARYING[]) but if you set VARCHAR to _1d_arr, the type of arr is VARCHAR(CHARACTER VARYING), then there is the error in the FOREACH statement.

• You must declare temp local variable in a DECLARE clause otherwise there is the error.

• My post explains DO statement.

• My post explains LOOP statement.

• A RAISE statement can raise an error or message.

• My answer explains how to create and use the 1D array of VARCHAR[].

• My answer explains how to create and use the 1D array of INT[].

Then, all elements are outputted as shown below:

INFO:  a
INFO:  b
INFO:  c
DO

Next, you can set SLICE 1 with temp of array type (VARCHAR[]) as shown below:

DO $$
DECLARE -- ↓ Here
  temp VARCHAR[];
  _1d_arr VARCHAR[] := ARRAY['a','b','c'];
BEGIN       -- ↓ ↓ ↓ ↓
  FOREACH temp SLICE 1 IN ARRAY _1d_arr LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

*Memos:

• The type of temp with SLICE 1 must not be non-array (VARCHAR) otherwise there is error.

• You can set VARCHAR[][], VARCHAR[][][], etc to temp, then the type of temp is automatically converted to VARCHAR[](CHARACTER VARYING[]).

Then, the whole 1D array is outputted as shown below:

INFO:  {a,b,c}
DO

Next, you can iterate a 2D(two-dimensional) array with a FOREACH statement 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
$$;

*Memos:

• For a 2D array, you can set SLICE 0, SLICE 1 or SLICE 2 so if you set SLICE 3, there is error.

• My post explains how to create and use a 2D array.

Then, all elements are outputted as shown below:

INFO:  a
INFO:  b
INFO:  c
INFO:  d
INFO:  e
INFO:  f
DO

Next, you can set SLICE 1 with temp of array type (VARCHAR[]) 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 1 IN ARRAY _2d_arr LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

Then, 2 arrays are outputted as shown below:

INFO:  {a,b,c}
INFO:  {d,e,f}
DO

Next, you can set SLICE 2 with temp of array type (VARCHAR[]) 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 2 IN ARRAY _2d_arr LOOP
    RAISE INFO '%', temp;
  END LOOP;
END
$$;

Then, the whole 2D array is outputted as shown below:

INFO:  {{a,b,c},{d,e,f}}
DO

In addition, you can use a FOR statement with a LOOP statement to iterate a 1D array as shown below. *My post explains FOR statement:

DO $$
DECLARE
  _1d_arr VARCHAR[] := ARRAY['a','b','c'];
BEGIN
  FOR num IN 1..3 LOOP
    RAISE INFO '%', _1d_arr[num];
  END LOOP;
END
$$;

Or, you can use array_lower() and array_upper() as shown below:

DO $$
DECLARE
  _1d_arr VARCHAR[] := ARRAY['a','b','c'];
BEGIN
  FOR num IN array_lower(_1d_arr, 1)..array_upper(_1d_arr, 1) LOOP
    RAISE INFO '%', _1d_arr[num];
  END LOOP;
END
$$;

*Memos:

• You can replace array_upper() with array_length().

• The doc explains array_lower(), array_upper() and array_length() in detail

Then, all elements are outputted as shown below:

INFO:  a
INFO:  b
INFO:  c
DO

And, you can use a FOR statement with a LOOP 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
$$;

Or, you can use array_lower() and array_upper() as shown below. *You can replace array_upper() with array_length():

DO $$
DECLARE
  _2d_arr VARCHAR[] := ARRAY[
    ['a','b','c'],
    ['d','e','f']
  ];
BEGIN
  FOR num1 IN array_lower(_2d_arr, 1)..array_upper(_2d_arr, 1) LOOP
    FOR num2 IN array_lower(_2d_arr, 2)..array_upper(_2d_arr, 2) LOOP
      RAISE INFO '%', _2d_arr[num1][num2];
    END LOOP;
  END LOOP;
END
$$;

Then, all elements are outputted as shown below:

INFO:  a
INFO:  b
INFO:  c
INFO:  d
INFO:  e
INFO:  f
DO