Using Loops: LOOP, FOR, WHILE in PL/pgSQL

Loops let you run a block of code several times in a row, either while a certain condition is true or while you're iterating over some set of data. This is super useful for automating tasks, handling big chunks of data, or doing repetitive stuff.

Imagine you had to hand out candy to every kid in a group by hand. That would be chaos! Instead, you can just go through the list of kids and give each one a candy in turn. In this scenario, "going through the list" is a loop.

PostgreSQL supports a few types of loops:

1. LOOP — a universal loop that runs until you explicitly stop it.
2. FOR — a loop with a set number of iterations over a range of numbers or a query result.
3. WHILE — a loop that runs as long as a condition is true.

Let's break down each type separately.

Infinite Loop: LOOP

LOOP is the basic form of a loop in PL/pgSQL that just keeps repeating a block of code. This loop runs forever, and you have to stop it manually with the EXIT command.

Syntax:

LOOP
    -- Your code here
END LOOP;

Here's an example. Let's count the sum of numbers from 1 to 10 using LOOP:

DO $$
DECLARE
    counter INT := 1;
    sum INT := 0;
BEGIN
    LOOP
        sum := sum + counter; -- add the current counter value
        counter := counter + 1; -- increment the counter

        -- End the loop if the counter is greater than 10
        IF counter > 10 THEN
            EXIT;
        END IF;
    END LOOP;

    RAISE NOTICE 'Sum of numbers from 1 to 10: %', sum;
END $$;

1. The counter variable increases on each iteration.
2. The condition IF counter > 10 THEN EXIT; ends the loop when the counter goes over 10.
3. At the end, it prints out the sum of the numbers.

Looping Over a Range or Data Set: FOR

The second type of loop, FOR, is used to iterate over:

1. A range of numbers.
2. The result of an SQL query.

Looping Over a Range of Numbers

Syntax:

FOR variable IN [REVERSE] start..end LOOP
    -- Your code here
END LOOP;

Let's print numbers from 1 to 5:

DO $$
BEGIN
    FOR i IN 1..5 LOOP
        RAISE NOTICE 'Current value: %', i;
    END LOOP;
END $$;

Example with reverse order:

DO $$
BEGIN
    FOR i IN REVERSE 5..1 LOOP
        RAISE NOTICE 'Reverse order: %', i;
    END LOOP;
END $$;

Looping Over the Result of an SQL Query

This version is handy for processing table rows.

Syntax:

FOR variable IN SELECT ... LOOP
    -- Your code here
END LOOP;

Here's an example of iterating over the rows of the students table:

DO $$
DECLARE
    student_name TEXT;
BEGIN
    FOR student_name IN SELECT name FROM students LOOP
        RAISE NOTICE 'Hey, %!', student_name;
    END LOOP;
END $$;

Conditional Loop: WHILE

WHILE runs as long as the given condition is true.

Syntax:

WHILE condition LOOP
    -- Your code here
END LOOP;

Let's count the sum of numbers from 1 to 10 using WHILE:

DO $$
DECLARE
    counter INT := 1;
    sum INT := 0;
BEGIN
    WHILE counter <= 10 LOOP
        sum := sum + counter;
        counter := counter + 1;
    END LOOP;

    RAISE NOTICE 'Sum of numbers from 1 to 10: %', sum;
END $$;

Real-Life Examples

Now that we've got the basics of loops down, let's check out some real-life examples of how to use them.

Example 1: generating a multiplication table

DO $$
DECLARE
    i INT;
    j INT;
BEGIN
    FOR i IN 1..5 LOOP
        FOR j IN 1..5 LOOP
            RAISE NOTICE '% x % = %', i, j, i * j;
        END LOOP;
    END LOOP;
END $$;

Example 2: iterating over query results. Let's say we have a products table with id and price fields. We'll update all the prices by increasing them by 10%.

DO $$
DECLARE
    prod RECORD;
BEGIN
    FOR prod IN SELECT id, price FROM products LOOP
        UPDATE products
        SET price = prod.price * 1.1
        WHERE id = prod.id;
    END LOOP;
END $$;

Common Mistakes and How to Avoid Them

Getting stuck in a LOOP or WHILE. If you forget to add an exit condition (EXIT or a proper condition in WHILE), the loop will never end. It's kinda like driving a car with no brakes.

Mistake when iterating over a query result. If the structure of the result data doesn't match what you expect, you might run into an error in the FOR ... IN SELECT loop.

Non-optimal queries inside loops. For example, running an UPDATE in every iteration can really slow things down. In these cases, it's better to use a single SQL query instead of a loop.