PL/pgSQL – LOOP Statement
In PostgreSQL’s PL/pgSQL, you can use loop statements to create loops and iterate over a sequence of statements. PL/pgSQL provides several types of loop statements, including LOOP, WHILE, and FOR, each with its own use cases.
Here, we’ll focus on the LOOP statement, which creates an unconditional loop that continues until explicitly terminated using the EXIT statement.
Here’s the basic structure of the LOOP statement:
LOOP-- Statements to execute repeatedly-- You can include conditional statements and logic-- Use EXIT to terminate the loop when a condition is metEND LOOP;
Within the loop block, you can include any combination of PL/pgSQL statements, including conditional statements (IF), database queries, assignments, and other logic. The loop continues until you use the EXIT statement to break out of it.
Here’s a simple example of a LOOP statement 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
LOOP
RAISE NOTICE 'Current value of i: %', i;
i := i + 1;
IF i > 5 THEN
EXIT;
END IF;
END LOOP;
END;
$$;
In this example:
- We initialize a variable
ito 1. - We enter a
LOOPblock where we raise a notice showing the current value ofi. - After displaying the value, we increment
iby 1. - We use an
IFstatement to check ifihas reached 5. If it has, we use theEXITstatement to terminate the loop. - The loop continues until
ireaches 5, at which point it exits.
You can call this procedure to observe the loop in action:
CALL count_and_print();
The procedure will raise notices showing the values of i from 1 to 5.
The LOOP statement is useful for scenarios where you need to repeatedly execute a block of code until a specific condition is met. Depending on your requirements, you may also consider using WHILE or FOR loops in PL/pgSQL for different looping behaviors.