LOOP, EXIT, CONTINUE and WHILE statements in PostgreSQL
Tags: postgres, exit, continue, while
Published: 2024-02-08 19:21:21

*Memos:

- [My post](https://dev.to/hyperkai/for-statement-in-postgresql-5d19) explains `FOR` statement.

- [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) statements with a 1D and 2D array.

### <**LOOP statement**>

A [LOOP statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-LOOP):

- can keep running zero or more SQL queries in it.

- 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 with a `LOOP` statement as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    RAISE INFO '%', num;
    num := num + 1;
  END LOOP;
END
$$;
```

*Memos:

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

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

Then, it causes an infinite loop, then the infinite loop is automatically stopped(killed) with [the error](https://stackoverflow.com/questions/13114101/postgresql-error-canceling-statement-due-to-user-request#77882968) as shown below. *[My answer](https://dba.stackexchange.com/questions/213399/kill-infinite-loop-plperl#335353) explains how to manually stop(kill) an infinite loop on psql:

```lang-none
INFO:  0
INFO:  1
INFO:  2
...
INFO:  86760
INFO:  86761
INFO:  86762
ERROR:  canceling statement due to user request
CONTEXT:  PL/pgSQL function inline_code_block line 7 at RAISE
```

### <**EXIT statement**>

An [EXIT statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-EXIT):

- can exit the loop anytime or when the condition is true.

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

Now, you can use an `EXIT` statement in a `LOOP` statement as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    EXIT; -- Here
    RAISE INFO '%', num;
    num := num + 1;
  END LOOP;
END
$$;
```

Then, it doesn't raise any messages in the loop because the `EXIT` statement exits the loop just before the `RAISE` statement as shown below:

> DO

Next, you can use a `WHEN` clause with the condition `num = 3` as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    EXIT WHEN num = 3; -- Here
    RAISE INFO '%', num;
    num := num + 1;
  END LOOP;
END
$$;
```

*You can replace `EXIT WHEN ...` with `IF num ...` as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    -- EXIT WHEN num = 3;
    IF num = 3 THEN -- Here
      EXIT;         -- Here
    END IF;         -- Here
    RAISE INFO '%', num;
    num := num + 1;
  END LOOP;
END
$$;
```

Then, it raises the messages in the loop, then exits the loop when `num = 3` as shown below:

```lang-none
INFO:  0
INFO:  1
INFO:  2
DO
```

Next, you can exit inner and outer loops when `num >= 2` and `num = 4` respectively as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    LOOP
      EXIT WHEN num >= 2; -- Here
      RAISE INFO 'Inner loop:%', num;
      num := num + 1;
    END LOOP;
    EXIT WHEN num = 4; -- Here
    RAISE INFO 'Outer loop:%', num;
    num := num + 1;
  END LOOP;
END
$$;
```

Then, it raises the messages in the inner and outer loops, then exits the inner and outer loops when `num >= 2` and `num = 4` respectively as shown below:

```lang-none
INFO:  Inner loop:0
INFO:  Inner loop:1
INFO:  Outer loop:2
INFO:  Outer loop:3
DO
```

### <**CONTINUE statement**>

A [CONTINUE statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-CONTINUE):

- can exit the current iteration of the loop anytime or when the condition is true, then starts the next iteration of the loop.

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

Now, you can use a `CONTINUE` statement in a `LOOP` statement as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    num := num + 1;
    EXIT WHEN num = 4;
    CONTINUE; -- Here
    RAISE INFO '%', num;
  END LOOP;
END
$$;
```

Then, it doesn't raise any messages in the loop because the `CONTINUE` statement exits the current iteration of the loop just before the `RAISE` statement, then starts the next iteration of the loop as shown below:

> DO

Next, you can use a `WHEN` clause with the condition `num = 2` as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    num := num + 1;
    EXIT WHEN num = 4;
    CONTINUE WHEN num = 2; -- Here
    RAISE INFO '%', num;
  END LOOP;
END
$$;
```

*You can replace `CONTINUE WHEN ...` with `IF num ...` as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    num := num + 1;
    EXIT WHEN num = 4;
    -- CONTINUE WHEN num = 2;
    IF num = 2 THEN -- Here
      CONTINUE;     -- Here
    END IF;         -- Here
    RAISE INFO '%', num;
  END LOOP;
END
$$;
```

Then, it raises the messages in the loop except when `num = 2` as shown below:

```lang-none
INFO:  1
INFO:  3
DO
```

Next, you can exit the current iteration of inner and outer loops when `num = 2` and `num = 4` respectively as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  LOOP
    LOOP
      num := num + 1;
      EXIT WHEN num >= 3;
      CONTINUE WHEN num = 2; -- Here
      RAISE INFO 'Inner loop:%', num;
    END LOOP;
    EXIT WHEN num >= 6;
    CONTINUE WHEN num = 4; -- Here
    RAISE INFO 'Outer loop:%', num;
  END LOOP;
END
$$;
```

Then, it raises the messages in the inner and outer loops except when `num = 2` and `num = 4` respectively as shown below:

```lang-none
INFO:  Inner loop:1
INFO:  Outer loop:3
INFO:  Outer loop:5
DO
```

### <**WHILE statement**>

A [WHILE statement](https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS-WHILE):

- can repeat a `LOOP` statement as long as the condition is true.

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

Now, you can use the `WHILE` statement whose condition is `num < 3` with a `LOOP` statement as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  WHILE num < 3 LOOP
    RAISE INFO '%', num;
    num := num + 1;
  END LOOP;
END
$$;
```

Then, it raises the messages in the loop, then exits the loop while `num < 3` as shown below:

```lang-none
INFO:  0
INFO:  1
INFO:  2
DO
```

Next, you can use the inner and outer `WHILE` statements whose conditions are `num < 3` and `num < 5` respectively as shown below:

```sql
DO $$
DECLARE
  num INT := 0;
BEGIN
  WHILE num < 5 LOOP
    WHILE num < 3 LOOP
      RAISE INFO 'Inner loop:%', num;
      num := num + 1;
    END LOOP;
    RAISE INFO 'Outer loop:%', num;
    num := num + 1;
  END LOOP;
END
$$;
```

Then, it raises the messages in the inner and outer loops while `num < 3` and `num < 5` respectively as shown below:

```lang-none
INFO:  Inner loop:0
INFO:  Inner loop:1
INFO:  Inner loop:2
INFO:  Outer loop:3
INFO:  Outer loop:4
DO
```