PostgreSQL - Continue

In the context of PostgreSQL, CONTINUE is often referred to in the context of PL/pgSQL, which is PostgreSQL's procedural language. Within PL/pgSQL, CONTINUE is used in looping constructs to skip the current iteration and move to the next one.

Basic Syntax:

Inside a loop, you can use the CONTINUE keyword to skip the current iteration of the loop.

WHILE condition LOOP
    -- some code
    IF some_other_condition THEN
        CONTINUE;
    END IF;
    -- some more code
END LOOP;

In this example, if some_other_condition is true during an iteration of the WHILE loop, the CONTINUE statement will be executed, skipping any code after it and proceeding to the next iteration of the loop.

Example:

Here's a practical example of using CONTINUE in a loop. Imagine you have a table numbers with a single column value storing integers. You want to sum all positive numbers in this table:

DO $$ 
DECLARE 
    sum_positive INTEGER := 0; 
    rec RECORD;
BEGIN 
    FOR rec IN SELECT value FROM numbers LOOP 
        IF rec.value <= 0 THEN 
            CONTINUE; 
        END IF; 
        sum_positive := sum_positive + rec.value; 
    END LOOP; 
    RAISE NOTICE 'The sum of positive numbers is %', sum_positive;
END $$;

In this PL/pgSQL block, the code loops through each number in the numbers table. If the number is non-positive, it uses CONTINUE to skip the current iteration and proceed to the next number. Otherwise, it adds the number to the sum_positive variable.

The RAISE NOTICE command is used to print the result. If you were executing this in an environment like psql or through a PostgreSQL admin tool, you'd see the result as an informational message.

Remember, CONTINUE is specific to PL/pgSQL's control structures and is not used in standard SQL queries.

Using CONTINUE in PL/pgSQL loops in PostgreSQL:

Description: CONTINUE is used to skip the rest of the loop's body and start the next iteration.

Code:

CREATE OR REPLACE FUNCTION example_loop()
RETURNS VOID AS $$
DECLARE
    counter INTEGER := 1;
BEGIN
    LOOP
        -- Some condition to exit the loop
        IF counter > 5 THEN
            EXIT;
        END IF;
        
        -- Some logic
        RAISE NOTICE 'Processing iteration %', counter;
        
        -- Increment counter
        counter := counter + 1;

        -- Skip the rest of the loop if the condition is met
        IF counter = 3 THEN
            CONTINUE;
        END IF;

        -- More logic that won't be executed when counter is 3
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Skipping the rest of the loop with CONTINUE in PostgreSQL:

Description: CONTINUE is used to skip the remaining part of the loop and start the next iteration.

Code:

FOR i IN 1..10 LOOP
    -- Some logic before the condition
    
    -- Skip the rest of the loop if the condition is met
    IF i = 5 THEN
        CONTINUE;
    END IF;
    
    -- More logic that won't be executed when i is 5
END LOOP;

CONTINUE vs. EXIT in PostgreSQL loops:

Description: CONTINUE skips the rest of the loop and starts the next iteration, while EXIT terminates the loop entirely.

Code:

FOR i IN 1..10 LOOP
    -- Some logic before the condition
    
    -- Skip the rest of the loop if the condition is met
    IF i = 5 THEN
        CONTINUE;
    END IF;

    -- Terminate the loop if another condition is met
    IF i = 8 THEN
        EXIT;
    END IF;
    
    -- More logic that won't be executed when i is 5
END LOOP;

Handling nested loops with CONTINUE in PostgreSQL:

Description: CONTINUE can be used within nested loops to skip the remaining part of the inner loop.

Code:

FOR i IN 1..5 LOOP
    FOR j IN 1..5 LOOP
        -- Some logic before the condition
        
        -- Skip the rest of the inner loop if the condition is met
        IF j = 3 THEN
            CONTINUE;
        END IF;
        
        -- More logic that won't be executed when j is 3
    END LOOP;
END LOOP;

Using labels with CONTINUE in PostgreSQL:

Description: Labels can be used to specify which loop to continue in case of nested loops.

Code:

<<outer_loop>>
FOR i IN 1..5 LOOP
    <<inner_loop>>
    FOR j IN 1..5 LOOP
        -- Some logic before the condition
        
        -- Skip the rest of the inner loop if the condition is met
        IF j = 3 THEN
            CONTINUE inner_loop;
        END IF;
        
        -- More logic that won't be executed when j is 3
    END LOOP inner_loop;
END LOOP outer_loop;

CONTINUE in exception blocks in PostgreSQL:

Description: CONTINUE can be used within exception blocks to skip the rest of the loop when an exception occurs.

Code:

FOR i IN 1..10 LOOP
    BEGIN
        -- Some logic that might raise an exception
        IF i = 5 THEN
            RAISE EXCEPTION 'Exception at iteration %', i;
        END IF;
        
        -- More logic that won't be executed when an exception occurs
    EXCEPTION
        WHEN OTHERS THEN
            -- Handle the exception
            CONTINUE;
    END;
END LOOP;

CONTINUE vs RETURN in PL/pgSQL functions in PostgreSQL:

Description: CONTINUE skips the rest of the loop and starts the next iteration, while RETURN exits the entire function.

Code:

CREATE OR REPLACE FUNCTION example_function()
RETURNS VOID AS $$
DECLARE
    counter INTEGER := 1;
BEGIN
    LOOP
        -- Some condition to exit the loop
        IF counter > 5 THEN
            RETURN;
        END IF;
        
        -- Some logic
        RAISE NOTICE 'Processing iteration %', counter;
        
        -- Increment counter
        counter := counter + 1;

        -- Skip the rest of the loop if the condition is met
        IF counter = 3 THEN
            CONTINUE;
        END IF;

        -- More logic that won't be executed when counter is 3
    END LOOP;
END;
$$ LANGUAGE plpgsql;