How to Use CONTINUE Statement in a Loop in PostgreSQL

PostgreSQL supports a “continue” statement that lets us skip the loop’s current iteration and proceed to the subsequent iteration. This statement can be used with all types of loops, such as for, while, and unconditional loops. A continue statement can save time by skipping unnecessary data/iterations.

This guide will explain how to continue a loop in PostgreSQL.

How to Use CONTINUE Statement in a Loop in PostgreSQL

Loops are used to perform similar tasks again and again until the conditions remain true and it will immediately stop when it becomes false. The “continue” statement can be embedded with the loop statement to skip the iteration satisfying the condition and jump to the next one.

The given code/syntax demonstrates how to use the continue statement inside the loop in PostgreSQL:

continue [label] [when condition]

Syntax Explanation:

The continue keyword starts the statement, followed by the label of the loop in which the continue statement is being used.
After mentioning the label, type the condition statement/block for which the loop will continue/skip the iteration.

Example 1: Iterate Using Continue Statement

The following code uses a continue statement in the loop to print even numbers between 0-15:

DO
$$
DECLARE
   num INTEGER = 0;
BEGIN
  RAISE NOTICE 'Even Numbers Between 0-15 are ==>';
  LOOP
     num = num + 1;
 EXIT WHEN num > 15;
 CONTINUE WHEN mod(num,2) != 0;
 RAISE NOTICE '%', num;
  END LOOP;
END;
$$

Code Explanation

First, we declare a variable “num” of type “INTEGER” and assign it with a value “0” (loop start point).
Next, we use the “RAISE NOTICE” statement to print a message “Even Numbers Between 0-15 are ==>”.
After this, we start a loop that increments the variable’s value by 1 in each iteration. Also, we use the “EXIT” keyword to exit/terminate a loop when the variable’s value exceeds 15.
Next, we use the “CONTINUE” statement that skips the value when it is completely divided by the number 2 (this way, the odd numbers will be skipped).
Finally, we use the “RAISE NOTICE” statement to print the even values between 0 and 15:

Example 2: Use Continue on PostgreSQL Table

Let’s learn how to use a continue statement inside the loop to iterate over a PostgreSQL table; but first, use the following query to get the data from the “bike_details” table:

SELECT * FROM bike_details;

Use the following query to use a continue statement in a loop working on the PostgreSQL table:

DO $$
DECLARE
  price_details RECORD;
BEGIN
  FOR price_details IN SELECT * FROM bike_details LOOP
   IF price_details.bike_price < 110000 THEN
   CONTINUE;
  END IF;
     RAISE NOTICE 'Bike Number: % Bike Price %', 
     price_details.bike_number, price_details.bike_price;
  END LOOP;
END $$;

Code Explanation

First, we declare a variable named “price_details” of type “RECORD”.
Next, we use a for loop to iterate over each row retrieved by the SELECT query. It selects all columns from a table named "bike_details" and assigns each row to the "price_details" variable.
After this, we use the If statement to filter bike_details based on bike_price. It is such that, if the bike_price is less than 110000, the loop continues to the next iteration.
Finally, we use the “RAISE NOTICE” statement to print the bike_number and bike_price:

That’s all about using a “CONTINUE” statement in PostgreSQL.

Conclusion

In PostgreSQL, loops are used in the queries to perform single code/statements for multiple rows in the table. The continue statement can be integrated with the loop to skip the values that satisfy the condition mentioned in the block of the statement This guide has explained the use of a continue statement in PostgreSQL with the help of multiple examples.

How to Use CONTINUE Statement in a Loop in PostgreSQL