LOOP, EXIT, CONTINUE and WHILE statements in PostgreSQL

Buy Me a Coffee☕

*Memos:

• My post explains FOR statement.

• My post explains FOREACH and FOR statements with a 1D and 2D array.

<LOOP statement>

A LOOP statement:

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

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

Now, you can create the DO statement with a LOOP statement as shown below:

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

*Memos:

• My post explains DO statement.

• A RAISE statement can raise an error or message.

Then, it causes an infinite loop, then the infinite loop is automatically stopped(killed) with the error as shown below. *My answer explains how to manually stop(kill) an infinite loop on psql:

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:

• 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:

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:

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:

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:

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:

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:

INFO:  Inner loop:0
INFO:  Inner loop:1
INFO:  Outer loop:2
INFO:  Outer loop:3
DO

<CONTINUE statement>

A CONTINUE statement:

• 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:

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:

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:

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:

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:

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:

INFO:  Inner loop:1
INFO:  Outer loop:3
INFO:  Outer loop:5
DO

<WHILE statement>

A WHILE statement:

• 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:

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:

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:

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:

INFO:  Inner loop:0
INFO:  Inner loop:1
INFO:  Inner loop:2
INFO:  Outer loop:3
INFO:  Outer loop:4
DO

Comments

[CitronBrick]

Isn't this PL/SQL ? Why is the post titled PostgreSQL ?