PostgreSQL – Continue
The CONTINUE statement in PostgreSQL is used to prematurely skip the current iteration of a loop and proceed directly to the next iteration. This functionality applies to all types of loops, including unconditional loops, WHILE loops, and FOR loops.
Let us get a better understanding of the CONTINUE statement in PostgreSQL from this article.
CONTINUE Statement in PostgreSQL
The CONTINUE statement is used to skip the remaining statements in the current iteration and move to the next iteration. It can be used with all types of loops: unconditional, WHILE, and FOR loops.
Syntax
CONTINUE [ label ] [ WHEN boolean-expression ];
If we analyze the above syntax:
- label: (Optional) If a label is specified, the CONTINUE statement will refer to the loop associated with that label. If no label is provided, it applies to the innermost loop.
- WHEN condition: (Optional) A boolean expression that specifies when to skip the current iteration. If the condition evaluates to true, the loop iteration is skipped.
Both the label and WHEN condition is optional and may or may not be used with the continue statement.
PostgreSQL CONTINUE Statement Examples
Let us take a look at some of the examples of CONTINUE Statement in PostgreSQL to better understand the concept.
Example 1: Displaying Even Numbers from 1 to 10
The following example will be used to display the even numbers from 1 to 10.
Query:
do $$ declare cnt int = 0; begin loop -- increment of cnt cnt = cnt + 1; -- exit the loop if cnt > 10 exit when cnt > 10; -- skip the iteration if cnt is an odd number continue when mod(cnt,2) = 1; -- print out the cnt raise notice '%', cnt; end loop; end; $$;
Output:
Explanation: In the above example, we use the continue statement to skip the odd numbers by using the fact that the remainder when an odd number is divided by 2 is 1.
Example 2: Skipping a Specific Number
The following example will be used to display all numbers from 1 to 10 without displaying the number 6.
do $$ declare cnt int = 0; begin loop -- increment of cnt cnt = cnt + 1; -- exit the loop if cnt > 10 exit when cnt > 10; -- skip the iteration if cnt is an odd number continue when cnt = 6; -- print out the cnt raise notice '%', cnt; end loop; end; $$;
Output:
Explanation: In the above example, we use the continue statement to skip the iteration when the value of the ‘cnt’ variable reaches 6.
Important Points About PostgreSQL CONTINUE Statement
- The CONTINUE statement allows you to skip specific iterations based on a condition, helping to manage loop flow efficiently.
- In nested loops, labels help specify which loop to continue. Without a label, CONTINUE affects the innermost loop.
- Proper use of the CONTINUE statement can help avoid infinite loops by ensuring certain conditions are met before skipping iterations.
- Using the CONTINUE statement can improve loop efficiency by avoiding unnecessary processing, which can enhance the overall performance of your queries.
