PL/pgSQL LOOP Statement

Summary: In this tutorial, you’ll learn how to use the PostgreSQL LOOP statement to execute a code block repeatedly.

Overview of the PL/pgSQL LOOP statement #

The LOOP statement allows you to execute a code block repeatedly.

Here’s the basic syntax of the LOOP statement:

<<label>>
LOOP
    statement;
END LOOP [label];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify an optional loop label.
Next , start the loop by using the LOOP keyword.
Then, provide one or more statements to execute.
After that, use the END LOOP keywords to mark the end of the LOOP statement.
Finally, use the optional label after the END LOOP keyword.

The LOOP statement will execute the code block between LOOP and END LOOP repeatedly.

Typically, you specify a condition for terminating the loop using the IF and EXIT statement:

<<label>>
LOOP
    statement;
    IF condition THEN
        EXIT;
    END IF;
END LOOP [label];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

If the condition is true, the EXIT statement executes and terminates the loop.

The following EXIT statement with a loop label is equivalent to the IF and EXIT statement:

EXIT label WHEN condition;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that the label is optional. Therefore, you can make it shorter like this:

EXIT WHEN condition;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Besides the EXIT statement, you can use the RETURN statement to terminate the loop.

PL/pgSQL LOOP statement example #

The following example defines a count_down function that counts down from n to 1 using the LOOP statement:

CREATE OR REPLACE FUNCTION count_down(
    n INT
)
RETURNS VOID
AS
$$
DECLARE
    counter INT = n;
BEGIN
    IF n <= 0 THEN
        RETURN;
    END IF;

    LOOP
        RAISE NOTICE '%', counter;
        counter = counter - 1;
        IF counter = 0 THEN
            EXIT;
        END IF;
    END LOOP;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How the function works:

First, declare the counter variable in the declaration section and initialize its value to n: SQLcounter INT = n;
Second, exit the function if the n is less than or equal to zero: SQLIF n <= 0 THEN RETURN; END IF;
Third, output the value of the counter, decrease its value by one in each iteration, and exit the loop when the value of the counter is zero: SQLLOOP RAISE NOTICE '%', counter; counter = counter - 1; IF counter = 0 THEN EXIT; END IF; END LOOP;

The following statement uses the count_down function to count down from 10:

SELECT count_down(10);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  10
NOTICE:  9
NOTICE:  8
NOTICE:  7
NOTICE:  6
NOTICE:  5
NOTICE:  4
NOTICE:  3
NOTICE:  2
NOTICE:  1Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Using loop label example #

The following defines the count_up function that counts from 1 to n:

CREATE OR REPLACE FUNCTION count_up(
    n INT
)
RETURNS VOID
AS
$$
DECLARE
    counter INT = 1;
BEGIN
    IF n <= 0 THEN
        RETURN;
    END IF;

    <<counting>>
    LOOP
        RAISE NOTICE '%', counter;
        counter = counter + 1;
        EXIT counting WHEN counter > n;
    END LOOP;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this count_up function:

Use the counting loop label before the LOOP keyword.
Exit the loop specified by the counting loop label when the counter is greater than n.

The following example call the count_up function to count up from 1 to 3:

SELECT count_up(3);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  1
NOTICE:  2
NOTICE:  3Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Nested loop #

PL/pgSQL allows you to create a nested loop by placing a LOOP statement within another LOOP statement:

<<outer_loop>>
LOOP
    statement;
    <<inner_loop>>
    LOOP
        statement;
        EXIT inner_loop WHEN condition;
    END LOOP [inner_loop];
    EXIT outer_loop WHEN condition;
END LOOP [outer_loop];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following example uses a nested loop to generate a multiplication table:

DO $$
DECLARE
    v_row INT := 1;
    v_col INT := 1;
BEGIN
    <<outer_loop>>
    LOOP
        EXIT WHEN v_row > 10;
        v_col := 1;
        <<inner_loop>>
        LOOP
            EXIT WHEN v_col > 10;
            RAISE NOTICE '% * % = %', v_row, v_col, v_row * v_col;
            v_col := v_col + 1;
        END LOOP inner_loop;

        v_row := v_row + 1;
    END LOOP outer_loop;
END $$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example:

Both inner and outer loops iterate through the rows from 1 to 10.
Calculate and display the product for each combination of row and column.

Output:

NOTICE:  1 * 1 = 1
NOTICE:  1 * 2 = 2
NOTICE:  1 * 3 = 3
NOTICE:  1 * 4 = 4
NOTICE:  1 * 5 = 5
NOTICE:  1 * 6 = 6
NOTICE:  1 * 7 = 7
NOTICE:  1 * 8 = 8
NOTICE:  1 * 9 = 9
NOTICE:  1 * 10 = 10
NOTICE:  2 * 1 = 2
NOTICE:  2 * 2 = 4
NOTICE:  2 * 3 = 6
NOTICE:  2 * 4 = 8
NOTICE:  2 * 5 = 10
NOTICE:  2 * 6 = 12
NOTICE:  2 * 7 = 14
NOTICE:  2 * 8 = 16
NOTICE:  2 * 9 = 18
NOTICE:  2 * 10 = 20
...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use the PL/pgSQL LOOP statement to execute a code block repeatedly.
Use the EXIT statement with a condition to immediately exit a loop.
LOOP can be nested.

Quiz #

Was this tutorial helpful ?