PL/pgSQL FOR LOOP Statement

Summary: In this tutorial, you’ll learn how to use the PL/pgSQL FOR loop statement to iterate over a range of integers or rows of a result set.

Overview of PL/pgSQL FOR loop statement #

The FOR loop statement creates a loop that iterates over a range of integers or rows of a result set.

Here’s the basic syntax of the FOR loop statement that iterates over a range of integers from low to high:

<<label>>
FOR loop_counter IN low..high LOOP
    statements;
END LOOP [label];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

The FOR statement creates a loop counter variable (loop_counter) with the type integer. This variable can be accessed within the FOR loop.
The low and high can be values or expressions that specify the lower and upper bounds of the integer range. The FOR evaluates these expressions once before starting the loop. If low is greater than high, the FOR loop does not execute its body and does not issue any error.
The FOR loop assigns low to the loop_counter variable and increments its value by one after each iteration until the loop_counter equals high.

Basic FOR Loop example #

The following example uses a FOR loop to show three numbers from 1 to 3 to the output:

CREATE OR REPLACE FUNCTION show_number_inc()
RETURNS VOID
AS
$$
BEGIN
    FOR n IN 1..3 LOOP
        RAISE NOTICE '%', n;
    END LOOP;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How the function works:

First, use n as the loop counter to iterate over three integers from 1 to 3:

FOR n IN 1..3 LOOPCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, show a number using the RAISE NOTICE statement:

RAISE NOTICE '%', n;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The function displays three numbers: 1, 2, and 3:

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

Output:

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

Using REVERSE option #

If you use the REVERSE option, the FOR loop will decrement the loop counter after each iteration:

<<label>>
FOR loop_counter IN REVERSE high..low LOOP
    statements;
END LOOP [label];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Notice that the high and low are in reversed order.

For example, the following defines a function that uses the FOR loop to display three numbers from 3 to 1:

CREATE OR REPLACE FUNCTION show_number_dec()
RETURNS VOID
AS
$$
BEGIN
    FOR n IN REVERSE 3..1 LOOP
        RAISE NOTICE '%', n;
    END LOOP;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement calls the show_number_dec() function to display three numbers, 3, 2, and 1:

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

Output:

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

Customizing steps #

By default, the FOR loop increments or decrements the loop counter by one after each iteration.

If you want to change the step from 1 to another, you can use the BY option:

<<label>>
FOR loop_counter IN low..high [BY step] LOOP
    statements;
END LOOP [label];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

<<label>>
FOR loop_counter IN REVERSE high..low [BY step] LOOP
    statements;
END LOOP [label];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following defines a function that uses the FOR loop to show the even numbers between 0 and 10:

CREATE OR REPLACE FUNCTION show_even_numbers()
RETURNS VOID
AS
$$
BEGIN
    FOR n IN 0..10 BY 2 LOOP
        RAISE NOTICE '%', n;
    END LOOP;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this example, the show_even_numbers uses a FOR loop to iterate over even numbers from 0 to 10 with a step of 2 and RAISE NOTICE to print each even number.

The following statement calls the show_even_numbers() function:

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

Output:

NOTICE:  0
NOTICE:  2
NOTICE:  4
NOTICE:  6
NOTICE:  8
NOTICE:  10Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Looping over a result set #

To iterate over the rows of a result set, you use the following FOR loop statement:

[ <<label>> ]
FOR target IN query LOOP
    -- process individual row
END LOOP [ label ];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Suppose we have a products table:

The following defines a function that displays the top N most expensive products from the products table using the FOR loop statement:

CREATE OR REPLACE FUNCTION top_n(n INT) RETURNS VOID
AS
$$
DECLARE
    r record;
BEGIN
    FOR r IN SELECT product_name, price
                FROM products
                ORDER BY price DESC
                LIMIT n
    LOOP
        RAISE NOTICE '% - $%', r.product_name, r.price;
    END LOOP;
END;
$$
LANGUAGE PLPGSQL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works.

First, declare a variable of the record type in the declaration section:

r record;

The r is a variable of the record type. A record variable has no predefined structure. Instead, it inherits the structure of the row when the FOR statement assigns to it.

Second, use the FOR statement to iterate over rows returned by a query:

FOR r IN SELECT product_name, price
                FROM products
                ORDER BY price DESC
                LIMIT nCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The query returns the top n most expensive products.

Third, print the product name and price of the product in each iteration:

RAISE NOTICE '% - $%', r.product_name, r.price;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The following statement calls the top_n() function to display the top 3 most expensive products:

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

Output:

NOTICE:  Samsung QN900C Neo QLED - $2999.99
NOTICE:  Sony Bravia XR A95K - $2499.99
NOTICE:  LG G3 OLED - $2499.99Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

Use the FOR loop statement to iterate over a range of integers or rows of a result set.

Quiz #

Was this tutorial helpful ?