PostgreSQL Tutorial

Data Types

Querying & Filtering Data

Managing Tables

Modifying Data

Conditionals

Control Flow

Transactions & Constraints

Working with JOINS & Schemas

Roles & Permissions

Working with Sets

Subquery & CTEs

User-defined Functions

Important In-Built Functions

PostgreSQL PL/pgSQL

Variables & Constants

Stored Procedures

Working with Triggers

Working with Views & Indexes

Errors & Exception Handling

PostgreSQL - Loop Statement

In PostgreSQL's procedural language PL/pgSQL, loops allow you to execute a series of statements repeatedly. There are several types of loop statements available in PL/pgSQL:

1. Basic Loop:

The basic loop continues execution until an EXIT statement is encountered.

DO $$ 
DECLARE 
    counter INTEGER := 0; 
BEGIN 
    LOOP
        RAISE NOTICE 'Counter: %', counter; 
        counter := counter + 1; 
        EXIT WHEN counter >= 5; 
    END LOOP; 
END $$;

2. WHILE Loop:

The WHILE loop repeats while the given condition is true.

DO $$ 
DECLARE 
    counter INTEGER := 0; 
BEGIN 
    WHILE counter < 5 LOOP
        RAISE NOTICE 'Counter: %', counter; 
        counter := counter + 1; 
    END LOOP; 
END $$;

3. FOR Loop over a range of integers:

The FOR loop can iterate over a range of integers.

DO $$ 
DECLARE 
    counter INTEGER; 
BEGIN 
    FOR counter IN 1..5 LOOP
        RAISE NOTICE 'Counter: %', counter; 
    END LOOP; 
END $$;

4. FOR Loop over a result set:

You can also loop through a set of rows returned by a query.

DO $$ 
DECLARE 
    employee RECORD; 
BEGIN 
    FOR employee IN SELECT name, salary FROM employees WHERE salary > 50000 LOOP
        RAISE NOTICE 'Employee: % with Salary: %', employee.name, employee.salary; 
    END LOOP; 
END $$;

5. FOR Loop over an array:

Looping through array elements:

DO $$ 
DECLARE 
    element INTEGER;
    arr INTEGER[] := ARRAY[1,2,3,4,5];
BEGIN 
    FOREACH element IN ARRAY arr LOOP
        RAISE NOTICE 'Element: %', element; 
    END LOOP; 
END $$;

Exiting a Loop:

You can exit a loop prematurely using the EXIT statement. You can optionally associate the EXIT statement with a label to determine which loop to exit if you have nested loops.

label_name:
LOOP
    -- nested loop
    LOOP
        EXIT label_name; -- This will exit the outer loop
    END LOOP;
END LOOP;

Note:

All the examples given above are anonymous PL/pgSQL code blocks using the DO command. If you're creating functions or stored procedures, you'd embed these loops within those constructs instead.

Remember to be careful with loops, especially when modifying data or when working with large datasets, to avoid unintentional consequences or performance issues. Always test your loop logic thoroughly to ensure it behaves as expected.

How to use LOOP statement in PostgreSQL:

The LOOP statement is used for creating a loop in PostgreSQL.

CREATE OR REPLACE FUNCTION example_loop()
RETURNS VOID AS $$
BEGIN
   LOOP
      -- Your loop logic here
      EXIT WHEN condition; -- Exit the loop when a condition is met
   END LOOP;
END;
$$ LANGUAGE plpgsql;

Iterating over a range of values with LOOP in PostgreSQL:

Use LOOP to iterate over a range of values.

CREATE OR REPLACE FUNCTION loop_over_range()
RETURNS VOID AS $$
DECLARE
   i INT := 1;
BEGIN
   LOOP
      -- Your loop logic here
      EXIT WHEN i > 10; -- Exit the loop after 10 iterations
      i := i + 1;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

Nested LOOP statements in PostgreSQL:

Create nested loops for more complex iteration scenarios.

CREATE OR REPLACE FUNCTION nested_loop_example()
RETURNS VOID AS $$
DECLARE
   i INT := 1;
   j INT := 1;
BEGIN
   LOOP
      -- Outer loop logic
      EXIT WHEN i > 5;

      LOOP
         -- Inner loop logic
         EXIT WHEN j > 3;
         j := j + 1;
      END LOOP;

      i := i + 1;
      j := 1;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

Using CONTINUE statement in PostgreSQL LOOP:

Use the CONTINUE statement to skip the rest of the loop body and start the next iteration.

CREATE OR REPLACE FUNCTION loop_with_continue()
RETURNS VOID AS $$
DECLARE
   i INT := 1;
BEGIN
   LOOP
      -- Your loop logic here
      IF i = 3 THEN
         CONTINUE; -- Skip the rest of the loop body and start the next iteration
      END IF;

      -- Continue with the rest of the loop body
      EXIT WHEN i > 5;
      i := i + 1;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

Labels and named loops in PostgreSQL:

Use labels to name loops for better control flow.

CREATE OR REPLACE FUNCTION named_loop()
RETURNS VOID AS $$
DECLARE
   i INT := 1;
BEGIN
   main_loop: LOOP
      -- Your loop logic here
      EXIT main_loop WHEN i > 5;
      i := i + 1;
   END LOOP main_loop;
END;
$$ LANGUAGE plpgsql;

LOOP statement with EXIT WHEN in PostgreSQL:

Use EXIT WHEN to conditionally exit the LOOP statement.

CREATE OR REPLACE FUNCTION exit_condition_loop()
RETURNS VOID AS $$
DECLARE
   i INT := 1;
BEGIN
   LOOP
      -- Your loop logic here
      EXIT WHEN i > 5; -- Exit the loop when i exceeds 5
      i := i + 1;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

LOOP vs. WHILE in PostgreSQL:

LOOP and WHILE are both used for creating loops, with LOOP being more flexible.

CREATE OR REPLACE FUNCTION loop_vs_while()
RETURNS VOID AS $$
DECLARE
   i INT := 1;
BEGIN
   LOOP
      -- Your loop logic here
      EXIT WHEN i > 5;
      i := i + 1;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

Handling exceptions in LOOP statement in PostgreSQL:

Implement error handling within the LOOP statement using BEGIN...EXCEPTION...END.

CREATE OR REPLACE FUNCTION loop_with_exception_handling()
RETURNS VOID AS $$
DECLARE
   i INT := 1;
BEGIN
   LOOP
      BEGIN
         -- Your loop logic here
         EXIT WHEN i > 5;
         i := i + 1;
      EXCEPTION
         WHEN others THEN
            -- Handle exceptions
      END;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE example_procedure()
AS $$
DECLARE
   i INT := 1;
BEGIN
   LOOP
      -- Your loop logic here
      EXIT WHEN i > 5;
      i := i + 1;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION loop_with_exit_condition()
RETURNS VOID AS $$
DECLARE
   i INT := 1;
   should_exit BOOLEAN := FALSE;
BEGIN
   LOOP
      -- Your loop logic here
      should_exit := i > 5;
      EXIT WHEN should_exit;
      i := i + 1;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION loop_with_cursor()
RETURNS VOID AS $$
DECLARE
   cursor_variable CURSOR FOR SELECT * FROM your_table;
   record_variable your_table%ROWTYPE;
BEGIN
   OPEN cursor_variable;
   LOOP
      FETCH cursor_variable INTO record_variable;
      EXIT WHEN NOT FOUND;

      -- Your loop logic with record_variable
   END LOOP;
   CLOSE cursor_variable;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION dynamic_sql_loop()
RETURNS VOID AS $$
DECLARE
   sql_statement TEXT;
BEGIN
   LOOP
      -- Your loop logic here
      sql_statement := 'SELECT * FROM your_table WHERE condition';
      EXECUTE sql_statement;

      EXIT WHEN some_condition;
   END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION loop_with_fetch()
RETURNS VOID AS $$
DECLARE
   cursor_variable CURSOR FOR SELECT * FROM your_table;
   record_variable your_table%ROWTYPE;
BEGIN
   OPEN cursor_variable;
   LOOP
      FETCH cursor_variable INTO record_variable;
      EXIT WHEN NOT FOUND;

      -- Your loop logic with record_variable
   END LOOP;
   CLOSE cursor_variable;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION loop_with_break()
RETURNS VOID AS $$
DECLARE
   i INT := 1;
BEGIN
   LOOP
      -- Your loop logic here
      IF some_condition THEN
         BREAK; -- Exit the loop prematurely
      END IF;

      -- Continue with the rest of the loop body
      EXIT WHEN i > 5;
      i := i + 1;
   END LOOP;
END;
$$ LANGUAGE plpgsql;