How to Use Loops in PostgreSQL (with Examples)

Overview

Loops are a fundamental concept in any programming language, including SQL procedural languages. In PostgreSQL, the PL/pgSQL procedural language provides several looping constructs that resemble those in traditional programming languages like Python or Java. This article guides you through the use of loops in PostgreSQL with practical examples.

What is PL/pgSQL?

PL/pgSQL is a procedural language supported by the PostgreSQL database management system. It extends the standard SQL language with control structures, variables, and other programming language features. Loops in PL/pgSQL help you execute a block of statements repeatedly.

Types of Loops in PL/pgSQL

• FOR LOOP: Iterates over a range of integers or over the rows returned by a SELECT query.
• WHILE LOOP: Continues executing as long as a specified condition is true.
• LOOP: An unconditional loop that needs an explicit exit condition inside the loop body.

Using FOR LOOP

A FOR LOOP can be used to iterate through a sequence of numbers or to loop over the results of a query. Here are the basic syntaxes:

-- Looping over a range
FOR i IN 1..10 LOOP
  -- Body of the loop
END LOOP;

-- Looping over a query result
FOR record IN SELECT * FROM my_table LOOP
  -- Body of the loop using the record
END LOOP;

Example 1: Basic FOR LOOP Over Range

Let’s say we want to create a table that logs numbers from 1 to 5:

BEGIN;
DO $
DECLARE
  i INT;
BEGIN
  CREATE TABLE logs (log INT);
  FOR i IN 1..5 LOOP
    INSERT INTO logs VALUES (i);
  END LOOP;
END;
$;
COMMIT;

This will create a logs table and insert five rows into it with values from 1 to 5.

Example 2: FOR LOOP Over Query Results

Consider a scenario where you want to log the names of all employees from a table ’employees’ into another table ’employee_logs’:

BEGIN;
DO $
DECLARE
  employee RECORD;
BEGIN
  CREATE TABLE employee_logs (name TEXT);
  FOR employee IN SELECT name FROM employees LOOP
    INSERT INTO employee_logs VALUES (employee.name);
  END LOOP;
END;
$;
COMMIT;

Each name from the ’employees’ table is inserted into the ’employee_logs’ table.

Using WHILE LOOP

A WHILE LOOP continues until the specified condition is false. Here’s the syntax for a WHILE LOOP:

WHILE condition LOOP
  -- statements to execute
END LOOP;

Example 3: WHILE LOOP in Action

If you wish to insert records until a certain condition is met, you could use a WHILE LOOP. For example, inserting numbers into a table until the sum of all numbers is greater than 100:

BEGIN;
DO $
DECLARE
  sum INT := 0;
  i INT := 1;
BEGIN
  CREATE TABLE sum_logs (number INT);
  WHILE sum < 100 LOOP
    sum := sum + i;
    INSERT INTO sum_logs VALUES (i);
    i := i + 1;
  END LOOP;
END;
$;
COMMIT;

Using LOOP

The basic LOOP structure has no condition to start; it needs an EXIT statement to terminate the loop. Syntax:

LOOP
  -- statements
  EXIT WHEN condition;
END LOOP;

Example 4: Basic LOOP with EXIT

To continue the previous example, but using a basic LOOP structure, you would write:

BEGIN;
DO $
DECLARE
  sum INT := 0;
  i INT := 1;
BEGIN
  CREATE TABLE sum_logs (number INT);
  LOOP
    EXIT WHEN sum >= 100;
    sum := sum + i;
    INSERT INTO sum_logs VALUES (i);
    i := i + 1;
  END LOOP;
END;
$;
COMMIT;

Conclusion

In this article, we have explored the various types of loops available in PostgreSQL using PL/pgSQL. Understanding these constructs empowers you to write more efficient and cleaner code by repeating tasks without writing the same SQL commands over and over. Remember, while loops can simplify tasks, they can potentially lead to long-running transactions, so use them judiciously and always ensure you have an exit condition set to prevent indefinite loops.

With these examples, you should have a good foundation to start implementing loops in PostgreSQL. As you practice, you’ll find that these tools can handle a vast array of tasks within your databases, making your PostgreSQL-related code more robust and easier to maintain.