PL/pgSQL – WHILE Statement
In PostgreSQL’s PL/pgSQL, you can use the WHILE statement to create a loop that continues executing a block of code as long as a specified condition remains true. The WHILE loop is a conditional loop that evaluates the condition at the beginning of each iteration, and if the condition is true, it continues looping; otherwise, it exits.
Here’s the basic structure of the WHILE loop in PL/pgSQL:
WHILE condition LOOP -- Statements to execute repeatedly -- You can include conditional statements and logic END LOOP;
Within the WHILE loop block, you can include any combination of PL/pgSQL statements, including conditional statements (IF), database queries, assignments, and other logic. The loop continues as long as the specified condition remains true, and it exits when the condition becomes false.
Here’s a simple example of a WHILE loop that counts from 1 to 5 and prints each number:
CREATE OR REPLACE procedure count_and_print()
LANGUAGE plpgsql
AS $$
DECLARE
i INT := 1;
BEGIN
WHILE i <= 5 LOOP
RAISE NOTICE 'Current value of i: %', i;
i := i + 1;
END LOOP;
END;
$$;
In this example:
- We initialize a variable
ito 1. - We enter a
WHILEloop with the conditioni <= 5, which means the loop will continue as long asiis less than or equal to 5. - Inside the loop block, we raise a notice showing the current value of
i. - After displaying the value, we increment
iby 1. - The loop continues as long as
iis less than or equal to 5, and it exits whenibecomes greater than 5.
You can call this function to observe the WHILE loop in action:
CALL count_and_print();
The procedure will raise notices showing the values of i from 1 to 5.
The WHILE loop is a valuable tool for creating conditional loops in PL/pgSQL. It allows you to control the loop behavior based on a condition and execute code repeatedly until that condition is no longer met.