DEV Community

Super Kai (Kazuya Ito)
Super Kai (Kazuya Ito)

Posted on • Updated on

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
$$;
Enter fullscreen mode Exit fullscreen mode

*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
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

*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
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

*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
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

Then, 2 arrays are outputted as shown below:

INFO:  {a,b,c}
INFO:  {d,e,f}
DO
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

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

INFO:  {{a,b,c},{d,e,f}}
DO
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

*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
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

Then, all elements are outputted as shown below:

INFO:  a
INFO:  b
INFO:  c
INFO:  d
INFO:  e
INFO:  f
DO
Enter fullscreen mode Exit fullscreen mode

Top comments (0)