FOREACH and FOR statements with arrays in PostgreSQL

#postgres #foreach #for #array

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

Build apps, not infrastructure.

Dealing with servers, hardware, and infrastructure can take up your valuable time. Discover the benefits of Heroku, the PaaS of choice for developers since 2007.

Visit Site