PL/pgSQL WHILE Loop Statement

Summary: In this tutorial, you’ll learn how to execute a code block if a condition is true using the PL/pgSQL WHILE statement.

Overview of PL/pgSQL WHILE loop statement #

The WHILE statement allows you to execute a code block repeatedly if a condition is true.

Here’s the syntax of the WHILE statement:

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

The WHILE evaluates the condition. If the condition is true, the WHILE executes the statements.

It’ll re-evaluate the condition before each iteration and execute statements as long as it is true.

Since the WHILE statement evaluates the condition before each iteration, it is known as a pretest loop.

To avoid an indefinite loop, you may need to change variables to make the condition not true at some point.

Basic PL/pgSQL WHILE statement example #

The following example defines a function that calculates the sum of a sequence of integers from 1 to n:

CREATE OR REPLACE FUNCTION total(n INT)
RETURNS INT
AS
$$
DECLARE
    v_current INT = 1;
    v_result INT  = 0;
BEGIN
    WHILE v_current <= n LOOP
        v_result = v_result + v_current;
        v_current = v_current + 1;
    END LOOP;
    RETURN v_result;
END;
$$
LANGUAGE plpgsql;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How the function works:

First, declare the variables and initialize their values to one and zero:

v_current INT = 1;
v_result INT  = 0;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

We’ll use v_current to iterate from 1 to n and v_result to store the sum.

Second, add the value of the v_current to the v_result and increment v_current by one as long as the v_current is less than or equal to n:

WHILE v_current <= n LOOP
   v_result = v_result + v_current;
   v_current = v_current + 1;
END LOOP;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The loop effectively calculates the sum of all integers from 1 to n.

Third, return the total after the loop completes:

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

The following statement calls the total() function to calculate the sum of a sequence from 1 to 10:

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

Output:

 total
-------
    55
(1 row)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PL/pgSQL WHILE statement to execute a code block repeatedly if a condition is true.

Quiz #