DEV Community

Super Kai (Kazuya Ito)
Super Kai (Kazuya Ito)

Posted on • Updated on

LOOP, EXIT, CONTINUE and WHILE statements in PostgreSQL

Buy Me a Coffee

*Memos:

<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
$$;
Enter fullscreen mode Exit fullscreen mode

*Memos:

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
Enter fullscreen mode Exit fullscreen mode

<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
$$;
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

*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
$$;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

<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
$$;
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

*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
$$;
Enter fullscreen mode Exit fullscreen mode

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

INFO:  1
INFO:  3
DO
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

<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
$$;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
$$;
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Top comments (1)

Collapse
 
citronbrick profile image
CitronBrick

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