FOREACH and FOR statements with arrays in PostgreSQL
Tags: postgres, foreach, for, array
Published: 2024-02-04 16:25:40

A [FOREACH statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY): 

- can repeat a [LOOP statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-LOOP) as long as there are array values.

- can be used only with a PL/pgSQL function and procedure and [DO statement](https://www.postgresql.org/docs/current/sql-do.html).

Now, you can create the `DO` statement which has a `FOREACH` statement with a [LOOP statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-LOOP) to iterate a 1D(one-dimensional) array as shown below:

```sql
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](https://stackoverflow.com/questions/53033819/type-error-while-looping-through-array-in-pl-pgsql#77931176) in the `FOREACH` statement.

- You must declare `temp` local variable in a `DECLARE` clause otherwise there is [the error](https://dba.stackexchange.com/questions/64046/error-loop-variable-of-foreach-must-be-a-known-variable-or-list-of-variables#335496).

- [My post](https://dev.to/hyperkai/do-statement-in-postgresql-533p) explains `DO` statement.

- [My post](https://dev.to/hyperkai/loop-exit-continue-and-while-statements-in-postgresql-1gbo) explains `LOOP` statement.

- A [RAISE statement](https://www.postgresql.org/docs/current/plpgsql-errors-and-messages.html) can raise an error or message.

- [My answer](https://stackoverflow.com/questions/71406989/declare-an-array-in-postgresql#77723752) explains how to create and use the 1D array of `VARCHAR[]`.

- [My answer](https://stackoverflow.com/questions/11831555/get-nth-element-of-an-array-that-returns-from-string-to-array-function/#77723802) explains how to create and use the 1D array of `INT[]`.

Then, all elements are outputted as shown below:

```lang-none
INFO:  a
INFO:  b
INFO:  c
DO
```

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

```sql
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:

```lang-none
INFO:  {a,b,c}
DO
```

Next, you can iterate a 2D(two-dimensional) array with a `FOREACH` statement as shown below:

```sql
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](https://dev.to/hyperkai/a-multi-dimensional-array-in-postgresql-eak) explains how to create and use a 2D array.

Then, all elements are outputted as shown below:

```sql
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:

```sql
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:

```lang-none
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:

```sql
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:

```lang-none
INFO:  {{a,b,c},{d,e,f}}
DO
```

In addition, you can use a [FOR statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-INTEGER-FOR) with a `LOOP` statement to iterate a 1D array as shown below. *[My post](https://dev.to/hyperkai/for-statement-in-postgresql-5d19) explains `FOR` statement:

```sql
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:

```sql
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](https://www.postgresql.org/docs/current/functions-array.html) explains `array_lower()`, `array_upper()` and `array_length()` in detail

Then, all elements are outputted as shown below:

```lang-none
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:

```sql
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()`:

```sql
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:

```lang-none
INFO:  a
INFO:  b
INFO:  c
INFO:  d
INFO:  e
INFO:  f
DO
```