PostgreSQL – For Loops

PostgreSQL provides the for loop statements to iterate over a range of integers or over a result set or over the result set of a dynamic query. The different uses of the for loop in PostgreSQL are described below:

1. For loop to iterate over a range of integers

The syntax of the for loop statement to iterate over a range of integers:

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

If we analyse the above syntax:

• 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:

Flowchart of For loop

Example 1:

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:

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 3:

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, full_name
           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 ];

If we analyse the above syntax:

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

Example 4:

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 by 1: employee_id , 2: length of name 
    sort_type smallint := 1; 
    -- return the number of films
    rec_count int := 10;
    -- use to iterate over the film
    rec record;
    -- dynamic query
    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: