Sometimes everything in a loop goes according to plan—until you hit a reason to stop early. Luckily, PL/pgSQL gives us handy tools to manage this process.
Breaking a Loop with EXIT
Sometimes you need to finish a loop before it naturally reaches the end. This might happen if you find the value you need, hit an error, or want to stop iterating based on some condition. In these cases, we use EXIT.
EXIT is a way to "tell" the loop: "That's enough, you've done your job, time to stop."
The syntax for EXIT is super simple:
EXIT WHEN condition;
Here, the key phrase WHEN tells the loop under what condition it should end. If you don't specify a condition, EXIT just instantly stops the current loop.
Example: Ending a Loop When a Specific Value is Found
Let's say we have a column with student numbers, and we want to find a student with a specific ID. As soon as we find the student, we want to break out of the loop.
DO $$
DECLARE
student_id INT;
BEGIN
FOR student_id IN 1..100 LOOP
RAISE NOTICE 'Checking student ID: %', student_id;
-- If we found the right student, end the loop
IF student_id = 42 THEN
RAISE NOTICE 'Student with ID 42 found!';
EXIT;
END IF;
END LOOP;
END $$;
In this example, the loop goes through numbers from 1 to 100, checking each "student." As soon as ID 42 is found, the loop prints a message and stops running.
Skipping an Iteration with CONTINUE
Sometimes, inside a loop, you want to skip certain iterations but keep the loop going for the rest. This is especially useful if you want to ignore "unnecessary" data or skip steps for specific conditions.
CONTINUE says: "Okay, this condition doesn't work for us, let's just move on to the next iteration."
CONTINUE works just like EXIT, but instead of ending the loop, it skips the current iteration:
CONTINUE WHEN condition;
If the condition is true, the current iteration ends, and the loop jumps to the next one.
Example: Skipping Even Numbers
In this example, we'll loop through numbers from 1 to 10, but ignore even numbers and only print the odd ones.
DO $$
DECLARE
num INT;
BEGIN
FOR num IN 1..10 LOOP
-- Skip even numbers
IF num % 2 = 0 THEN
CONTINUE;
END IF;
RAISE NOTICE 'Odd number: %', num;
END LOOP;
END $$;Here, CONTINUE skips all iterations where num % 2 = 0 (i.e., the number is even). As a result, only odd numbers get printed to the log.
Combining EXIT and CONTINUE
You can use EXIT and CONTINUE together to control your loop more flexibly. For example, you might want to skip unnecessary iterations with CONTINUE, but break out of the whole loop if you find something important.
Here's an example where we skip all numbers divisible by 3, but end the loop as soon as we reach the number 16.
DO $$
DECLARE
num INT;
BEGIN
FOR num IN 1..20 LOOP
-- Skip numbers divisible by 3
IF num % 3 = 0 THEN
CONTINUE;
END IF;
-- End the loop as soon as we reach 16
IF num = 16 THEN
RAISE NOTICE 'Stopping at number %', num;
EXIT;
END IF;
RAISE NOTICE 'Current number: %', num;
END LOOP;
END $$;Here's how the loop works:
- Numbers divisible by 3 are skipped (
CONTINUE). - If the number is 16, the loop ends (
EXIT). - All other numbers are printed.
Advanced Example: Skipping Invalid Data and Stopping on a Critical Error
Now let's imagine a more real-world task. We want to process a list of students, checking their data. We'll skip invalid records, and if we hit a "critical error," we'll stop processing.
DO $$
DECLARE
student RECORD;
BEGIN
FOR student IN
SELECT * FROM students
LOOP
-- Skip records with invalid data
IF student.name IS NULL THEN
RAISE NOTICE 'Skipping student with ID %: Missing name', student.id;
CONTINUE;
END IF;
-- End the loop on a critical error
IF student.status = 'ERROR' THEN
RAISE EXCEPTION 'Critical error for student ID %', student.id;
EXIT; -- This line is actually redundant, since RAISE EXCEPTION stops execution.
END IF;
-- Process the record
RAISE NOTICE 'Processing student: %', student.name;
END LOOP;
END $$;In this example, CONTINUE helps skip students with missing names, and EXIT (together with RAISE EXCEPTION) ends the loop if a serious error is found.
Practical Tips and Common Mistakes
Don't forget about your condition logic. Using the wrong condition inside EXIT WHEN or CONTINUE WHEN can lead to unexpected behavior. For example, your loop might end too early or skip important data.
Don't overuse CONTINUE. If your code is full of CONTINUE checks, maybe it's time to rethink your loop logic and make it simpler.
Don't mix up EXIT and RETURN. EXIT only ends the current loop, while RETURN stops the whole function.
Watch out for infinite loops. If you use a LOOP without a clear exit condition, forget about EXIT, and your loop could run forever.
GO TO FULL VERSION