PostgreSQL - For Loops

In PostgreSQL, PL/pgSQL (Procedural Language/PostgreSQL) introduces control structures like FOR loops to simple complex data processing. The FOR loop allows developers to iterate over a specified range of integers or the results of a query and making repetitive tasks more manageable.

This feature is crucial for performing calculations, data manipulation and reporting efficiently. In this article, we will learn various FOR loop examples in PL/pgSQL by focusing on iterating over integers and explain their practical applications.

PostgreSQL For Loops

• In PostgreSQL, FOR loops are used to iterate over a range of integers, a result set or the result set of a dynamic query.
  
• This allows developers to execute a set of statements multiple times and make it easier to handle repetitive tasks or to process data in batches.

Types of FOR Loops in PostgreSQL

1. FOR Loop to Iterate Over a Range of Integers

This type of loop allows you to specify a range of integers and execute a block of code for each integer in that range. The REVERSE option allows iteration in reverse order. The BY clause specifies the increment step, defaulting to 1.

[ <<label>> ]
for loop_cnt in [ reverse ] from.. to [ by step ] loop
    statements
end loop [ label ];

Explanation:

• An integer variable loop_cnt is created at first, which is accessible inside the loop only. After each iteration, the for loop adds the step to the loop_cnt. However, when we use the reverse option, the for loop subtracts the step from loop_cnt after each iteration.
  
• To specify the lower and upper bound of the range, we use the from and to expressions. Before entering the loop, the for loop evaluates these expressions.
  
• The step that follows the by keyword specifies the iteration step with 1 as the default value. This step expression is evaluated only once.

The following flowchart describes the for loop statement:

Example 1: Iterate from 1 to 10

The following code uses the for loop statement to iterate over ten numbers from 1 to 10 and display each of them in each iteration:

DO $$
BEGIN
    FOR cnt IN 1..10 LOOP
        RAISE NOTICE 'cnt: %', cnt;
    END LOOP;
END; $$

Output:

Example 2: Iterate from 10 to 1

The following code uses the for loop statement to iterate over ten numbers from 10 to 1 and display each of them in each iteration:

DO $$
BEGIN
    FOR cnt IN REVERSE 10..1 LOOP
        RAISE NOTICE 'cnt: %', cnt;
    END LOOP;
END; $$

Output:

2. FOR Loop to Iterate Over a Result Set

The syntax of the for loop statement to iterate over a result set of a query:

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

First, we create a sample table using the below commands to perform examples:

CREATE TABLE employees (
  employee_id serial PRIMARY KEY,
  full_name VARCHAR NOT NULL,
  manager_id INT
);

Then we insert data into our employee table as follows:

INSERT INTO employees (
  employee_id,
  full_name,
  manager_id
)
VALUES
  (1, 'M.S Dhoni', NULL),
  (2, 'Sachin Tendulkar', 1),
  (3, 'R. Sharma', 1),
  (4, 'S. Raina', 1),
  (5, 'B. Kumar', 1),
  (6, 'Y. Singh', 2),
  (7, 'Virender Sehwag ', 2),
  (8, 'Ajinkya Rahane', 2),
  (9, 'Shikhar Dhawan', 2),
  (10, 'Mohammed Shami', 3),
  (11, 'Shreyas Iyer', 3),
  (12, 'Mayank Agarwal', 3),
  (13, 'K. L. Rahul', 3),
  (14, 'Hardik Pandya', 4),
  (15, 'Dinesh Karthik', 4),
  (16, 'Jasprit Bumrah', 7),
  (17, 'Kuldeep Yadav', 7),
  (18, 'Yuzvendra Chahal', 8),
  (19, 'Rishabh Pant', 8),
  (20, 'Sanju Samson', 8);

The table is:

Example 1: Iterate Over Employee IDs

The following code uses the for loop statement to iterate over largest 10 employee id:

DO $$
DECLARE
    f RECORD;
BEGIN
    FOR f IN SELECT employee_id, full_name 
              FROM employees 
              ORDER BY employee_id DESC 
              LIMIT 10 
    LOOP 
        RAISE NOTICE '% - %', f.employee_id, f.full_name;
    END LOOP;
END;
$$;

Output:
 

3. FOR Loop to Iterate Over the Result Set of a Dynamic Query

The syntax of the for loop statement to iterate over a result set of a dynamic query:

[ <<label>> ]
FOR row IN EXECUTE query_expression [ USING query_param [, ...] ] LOOP
    statements
END LOOP [ label ];

Explanation:

• query_expression is an SQL statement.
• The USING clause is used to pass the query parameters.

Example 1: Dynamic Query with Sorting

The following code shows how to use the for loop statement to loop through a dynamic query. It has the following two configuration variables:

• sort_type: 1 to sort by employee id, 2 to sort by length of name
• rec_count: is the number of records to query from the table.

DO $$
DECLARE
    sort_type SMALLINT := 1; -- 1: employee_id, 2: length of name
    rec_count INT := 10;      -- number of records to query
    rec RECORD;
    query TEXT;
BEGIN
    query := 'SELECT full_name, employee_id FROM employees ';
    
    IF sort_type = 1 THEN
        query := query || 'ORDER BY employee_id DESC ';
    ELSIF sort_type = 2 THEN
        query := query || 'ORDER BY LENGTH(full_name) DESC ';
    ELSE 
        RAISE 'Invalid sort type %s', sort_type;
    END IF;

    query := query || ' LIMIT $1';

    FOR rec IN EXECUTE query USING rec_count LOOP
        RAISE NOTICE '% - %', rec.employee_id, rec.full_name;
    END LOOP;
END;
$$;

Output:

If we change the sort_type to 2, we’ll get the following output:

Conclusion

Overall, using the FOR loop in PostgreSQL enhances the capabilities of PL/pgSQL, allowing for efficient data manipulation and processing. Understanding the loop syntax is essential for implementing effective control structures within your database functions. Additionally, integrating dynamic SQL queries in PostgreSQL with loop statements empowers developers to create flexible and adaptable code that can respond to varying data requirements. Mastering the PostgreSQL loop statement not only improves productivity but also ensures that database operations are executed seamlessly and effectively.