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:
Last Updated :
01 Feb, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...