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:
Contact Us