FOR statement in PostgreSQL
Tags: postgres, forloop, for, loop
Published: 2024-02-08 19:06:06

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

- 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 values.

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

- can be run with a [SELECT](https://www.postgresql.org/docs/current/sql-select.html) or [INSERT](https://www.postgresql.org/docs/current/sql-insert.html), [UPDATE](https://www.postgresql.org/docs/current/sql-update.html) or [DELETE](https://www.postgresql.org/docs/current/sql-delete.html) statement with a [RETURNING clause](https://www.postgresql.org/docs/current/dml-returning.html).

- can be dynamically run with the [EXECUTE](https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN) which has a `SELECT` or `INSERT`, `UPDATE` or `DELETE` statement with a `RETURNING` clause. *[My post](https://dev.to/hyperkai/prepare-and-execute-statements-in-postgresql-3bhc) explains an `EXECUTE` statement.

- can iterate an array. *[My post](https://dev.to/hyperkai/foreach-and-for-statements-with-arrays-in-postgresql-2bnc) explains [FOREACH](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY) and [FOR](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-INTEGER-FOR) statement with a 1D and 2D array.

*[My post](https://dev.to/hyperkai/loop-exit-continue-and-while-statements-in-postgresql-1gbo) explains [LOOP](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-LOOP), [EXIT](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-EXIT), [CONTINUE](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-CONTINUE) and [WHILE](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-WHILE) statement.

Now, you can create the PL/pgSQL function `my_func()` with the `FOR` statement whose range is `1..3` and a `LOOP` statement as shown below:

```sql
CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  FOR num IN 1..3 LOOP
    RAISE INFO '%', num;
  END LOOP;
END
$$ LANGUAGE plpgsql;
```

*Memos:

- You don't need declare `num` local variable in a `DECLARE` clause but you can if you want

- [My post](https://dev.to/hyperkai/a-plpgsql-function-in-postgresql-3j9f) explains a PL/pgSQL function.

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

Then, calling `my_func()` raises 3 messages in the loop, then exits the loop when the iteration of the last value `3` is finished as shown below:

```lang-none
postgres=# SELECT my_func();
INFO:  1
INFO:  2
INFO:  3
 my_func
---------

(1 row)
```

Next, you can use `REVERSE` with the `FOR` statement whose range is `3..1` as shown below. *Be careful, if the range is `1..3`, no messages are raised:

```sql
CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  FOR num IN REVERSE 3..1 LOOP
    RAISE INFO '%', num;
  END LOOP;
END
$$ LANGUAGE plpgsql;
```

Then, calling `my_func()` raises 3 messages in the loop, then exits the loop when the iteration of the last value `1` is finished as shown below:

```lang-none
postgres=# SELECT my_func();
INFO:  3
INFO:  2
INFO:  1
 my_func
---------

(1 row)
```

Next, you can use a `BY` clause with the `FOR` statement whose range is `1..5`  as shown below:

```sql
CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  FOR num IN 1..5 BY 2 LOOP
    RAISE INFO '%', num;
  END LOOP;
END
$$ LANGUAGE plpgsql;
```

Then, calling `my_func()` raises 3 messages in the loop by 2 steps, then exits the loop when the iteration of the last value `5` is finished as shown below:

```lang-none
postgres=# SELECT my_func();
INFO:  1
INFO:  3
INFO:  5
 my_func
---------

(1 row)
```

Next, you can use the inner and outer `FOR` statements whose ranges are `3..3` and `1..2` respectively as shown below:

```sql
CREATE FUNCTION my_func() RETURNS VOID
AS $$
BEGIN
  FOR num IN 3..3 LOOP
    FOR num IN 1..2 LOOP
      RAISE INFO 'Inner loop:%', num;
    END LOOP;
    RAISE INFO 'Outer loop:%', num;
  END LOOP;
END
$$ LANGUAGE plpgsql;
```

Then, calling `my_func()` raises 3 messages in the inner and outer loops, then exits the loop when the iterations of the last values `2` and `3` are finished respectively as shown below:

```lang-none
postgres=# SELECT my_func();
INFO:  Inner loop:1
INFO:  Inner loop:2
INFO:  Outer loop:3
 my_func
---------

(1 row)
```

Next, you create `person` table as shown below:

```sql
CREATE TABLE person (
  id INT,
  name VARCHAR(20),
  age INT
);
```

Then, you insert 4 rows into `person` table as shown below:

```sql
INSERT INTO person (id, name, age) 
VALUES (1, 'John', 27), (2, 'David', 32), (3, 'Robert', 18), (4, 'Mark', 40);
```

Then, you can use the [FOR statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING) with a `SELECT` statement as shown below:

```sql
CREATE FUNCTION my_func(min INT, max INT) RETURNS VOID
AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN SELECT * FROM person WHERE age BETWEEN min AND max LOOP
    RAISE INFO '%', row;
  END LOOP;
END
$$ LANGUAGE plpgsql;
```

*Memos:

- You must declare `row` local variable in a `DECLARE` clause otherwise there is [the error](https://stackoverflow.com/questions/15949337/psql-loop-variable-of-loop-over-rows-must-be-a-record-or-row-variable-or-list#77934106).

- You cannot use `REVERSE` with the `FOR` statement which has SQL otherwise there is error so instead, you can use `ORDER BY id DESC` for the `SELECT` statement. 

- You cannot use a `BY` clause with the `FOR` statement which has SQL otherwise there is error so instead, you can use `WHERE mod(id, 2) = 1` for the `SELECT` statement. *[The doc](https://www.postgresql.org/docs/current/functions-math.html) explains `mod()` in detail.

- You cannot use a [SELECT INTO statement](https://www.postgresql.org/docs/current/sql-selectinto.html) with a `FOR` statement otherwise there is the error(7).

- [My post](https://dev.to/hyperkai/return-next-statement-in-postgresql-1lmi) has the examples of a `FOR` statement with a [RETURN NEXT](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING-RETURN-NEXT) statement.

Or, you can use the `FOR` statement with an `EXECUTE` statement which has a `SELECT` statement as shown below:

```sql
CREATE FUNCTION my_func(min INT, max INT) RETURNS VOID
AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN EXECUTE 'SELECT * FROM person WHERE age BETWEEN $1 AND $2' USING min, max LOOP
    RAISE INFO '%', row;
  END LOOP;
END
$$ LANGUAGE plpgsql;
```

Then, calling `my_func()` raises 2 messages in the loop, then exits the loop when the iteration of the last row is finished as shown below:

```sql
postgres=# SELECT my_func(30, 40);
INFO:  (2,David,32)
INFO:  (4,Mark,40)
 my_func
---------

(1 row)
```

Next, you can use the `FOR` statement with a `UPDATE` statement as shown below. *You must set a `RETURNING` clause to the `UPDATE` statement with a `FOR` statement otherwise there is error:

```sql
CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN UPDATE person SET age = my_age WHERE id = my_id RETURNING * LOOP
    RAISE INFO '%', row;
  END LOOP;
END
$$ LANGUAGE plpgsql;
```

Or, you can use the `FOR` statement with an `EXECUTE` statement which has a `UPDATE` statement as shown below:

```sql
CREATE FUNCTION my_func(my_age INT, my_id INT) RETURNS VOID
AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN EXECUTE 'UPDATE person SET age = $1 WHERE id = $2 RETURNING *' USING my_age, my_id LOOP
    RAISE INFO '%', row;
  END LOOP;
END
$$ LANGUAGE plpgsql;
```

Then, it raises a message in the loop, then exits the loop when the iteration of the last row is finished as shown below:

```lang-none
postgres=# SELECT my_func(75, 2);
INFO:  (2,David,75)
 my_func
---------

(1 row)
```

Then, `age` of `David` is updated to `75` as shown below:

```sql
postgres=# SELECT * FROM person;
 id |  name  | age
----+--------+-----
  1 | John   |  27
  3 | Robert |  18
  4 | Mark   |  40
  2 | David  |  75
(4 rows)
```