PL/pgSQL EXIT

Summary: This tutorial explains how to use the PL/pgSQL EXIT statement to terminate loops prematurely.

Overview of the PL/pgSQL EXIT statement #

The EXIT statement terminates a loop, including LOOP, FOR LOOP, and WHILE LOOP.

Here’s the basic syntax of the EXIT statement:

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

To terminate based on a condition, you can use the following syntax:

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

In this syntax:

• label is the label of the loop you want to exit. If you omit the label, EXIT terminates the innermost enclosing loop.
• boolean_expression specifies a condition to terminate the loop.

This syntax is equivalent to the following:

IF boolean_expression THEN
    EXIT;
END IF;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Exiting a LOOP #

The following example shows how to use the EXIT statement to terminate a LOOP:

DO
$$
DECLARE
    counter INT = 0;
BEGIN
    LOOP
        counter := counter + 1;
        RAISE NOTICE '%', counter;
        EXIT WHEN counter >= 5;
    END LOOP;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

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

The loop prints numbers from 1 to 5. When the counter reaches 5, the condition in the EXIT statement becomes true, and the loop terminates.

Exiting a FOR LOOP #

The following example shows how to use the EXIT statement to terminate a FOR loop when the loop counter is greater than or equal to 5:

DO
$$
BEGIN
    FOR n IN 1..10 LOOP
        RAISE NOTICE '%', n;
        EXIT WHEN n >= 5;
    END LOOP;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

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

Exiting a WHILE LOOP #

The following example shows how to use the EXIT statement to terminate a WHILE loop when the loop counter is greater than or equal to 5:

DO
$$
DECLARE
    n INT = 0;
BEGIN
    WHILE n < 10 LOOP
        n := n + 1; -- Use := for assignment
        RAISE NOTICE '%', n;
        EXIT WHEN n >= 5;
    END LOOP;
END;
$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

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

Summary #

• Use the EXIT statement provides a way to prematurely terminate LOOP, FOR LOOP, and WHILE LOOP structures.

Quiz #