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