Recently, as a PostgreSQL developer, I got many requirements to use the PostgreSQL Loop in multiple real time scenarios. In this PostgreSQL tutorial, we will learn about the “Postgresql loop” for inserting records or accessing an array using a loop with multiple examples.
PostgreSQL Loop
In PostgreSQL, we can use a loop to perform the same task repeatedly or execute a series of statements.
Syntax
The syntax of the loop is given below.
FOR name IN expression .. expression
LOOP
statements
END LOOP;Where LOOP is the starting point of the loop, the statement is a command or any query that we want to execute repeatedly, and END LOOP is the ending point of the loop.
Here, we will loop through a table named major_cities that contains the population of major cities in the United Kingdom.
DO $$
DECLARE
city_names varchar;
BEGIN
FOR city_names IN SELECT city_name FROM major_cities
LOOP
RAISE NOTICE '%', city_names;
END LOOP;
END$$;
Types of Loops in PostgreSQL
PostgreSQL supports several types of loops in its PL/pgSQL procedural language. Each has its specific use case and syntax. Let’s explore them one by one.
1. Basic Loop
The basic loop is the simplest form of iteration in PostgreSQL. It continues indefinitely until explicitly terminated with an EXIT statement.
DO $$
DECLARE
counter INT := 0;
BEGIN
LOOP
counter := counter + 1;
RAISE NOTICE 'Counter: %', counter;
-- Exit condition
IF counter >= 5 THEN
EXIT;
END IF;
END LOOP;
END $$;
This loop will execute until the counter reaches 5. The EXIT statement acts as our break condition.
2. FOR Loop
The FOR loop is perfect when you need to iterate through a range of values or process query results.
Numeric Range FOR Loop
DO $$
BEGIN
FOR i IN 1..5 LOOP
RAISE NOTICE 'Iteration: %', i;
END LOOP;
END $$;
This loop will iterate from 1 to 5, inclusive, incrementing by 1 in each iteration.
Reverse FOR Loop
DO $$
BEGIN
FOR i IN REVERSE 5..1 LOOP
RAISE NOTICE 'Countdown: %', i;
END LOOP;
END $$;
This loop counts down from 5 to 1.
Query-Based FOR Loop
The FOR loop can iterate over query results, making it incredibly powerful for data processing:
DO $$
DECLARE
customer RECORD;
BEGIN
FOR customer IN SELECT customer_id, first_name, last_name FROM customers WHERE state = 'California' LOOP
RAISE NOTICE 'Processing customer: % %', customer.first_name, customer.last_name;
-- Additional processing here
END LOOP;
END $$;
This loop processes each California customer one by one.
3. WHILE Loop
The WHILE loop continues executing as long as a specified condition remains true.
DO $$
DECLARE
counter INT := 0;
BEGIN
WHILE counter < 5 LOOP
counter := counter + 1;
RAISE NOTICE 'Counter: %', counter;
END LOOP;
END $$;
In this example, the loop continues until the counter reaches 5.
4. FOREACH Loop
PostgreSQL provides a specialized loop for iterating through array elements called the FOREACH loop. This is particularly useful when working with array data types.
DO $$
DECLARE
names TEXT[] := ARRAY['John', 'Sarah', 'Michael', 'Jessica'];
name TEXT;
BEGIN
FOREACH name IN ARRAY names LOOP
RAISE NOTICE 'Processing: %', name;
END LOOP;
END $$;
This loop iterates through each element in the names array.
Example 1: Through query results
In PostgreSQL, whenever we perform a query, it returns a result set, which can be any table containing records, arrays, or a list of integers. So when we want to iterate or loop through these result sets, we can use a for loop.
The table that we will use in this section is the employee table, and a description of the table is given below.
Let’s run the SELECT statement to get the first_name of 5 employees as a query result and iterate or loop over it to print the first name.
DO $$
DECLARE
firstname varchar;
BEGIN
FOR firstname IN SELECT first_name FROM employee LIMIT 5
LOOP
RAISE NOTICE '%', firstname;
END LOOP;
END$$;In the above code, firstname is a variable of type varchar that contains the column data of first_name using the FOR statement.
From the above statement, we mean that the firstname stores the query result of the SELECT statement of 5 employees using LIMIT 5.
In the BEGIN and END blocks, we loop through the firstname variable to retrieve the first name of the employees using the LOOP and END LOOP blocks.
After executing the above query, I got the expected output as shown in the screenshot below.
Example 2: Through records
In PostgreSQL, a record is a group of data or a total set of fields, and we can loop or iterate through records.
Let’s loop through the first name and last_name of the employee and show the full name.
DO $$
DECLARE
emp_name record;
BEGIN
FOR emp_name IN SELECT first_name, last_name FROM employee LIMIT 10
LOOP
RAISE NOTICE '% %', emp_name.first_name,emp_name.last_name;
END LOOP;
END$$;In the above code, emp_name is a variable of record type that contains the column data of first_name and last_name using the FOR statement.
From the above statement, we mean that emp_name stores the query result of the SELECT statement for 10 employees, using the LIMIT 10 clause.
In the BEGIN and END blocks, we loop through emp_name to retrieve the first_name and last_name of the employee using the LOOP and END LOOP blocks.
After executing the above query, I got the expected output as shown in the screenshot below.
The above output shows the full names of 10 employees by looping through the query results.
Example 3: Through date range
In PostgreSQL, we can iterate or loop through a range of dates. Suppose we want to print every date from today to the next 10 days.
Consider that we have a date column and we want to print a range of dates; all of this can be done with the help of a loop.
Here, we will use the same employee table that we used in the previous subtopic.
Let’s print the hire dates of employees from June 1, 1991, to October 10, 1995, using the code below.
DO $$
DECLARE
hiredate date;
BEGIN
FOR hiredate IN SELECT hire_date
FROM employee
WHERE '[1991-06-01, 1994-01-26]'::daterange @> hire_date LIMIT 15
LOOP
RAISE NOTICE '%', hiredate;
END LOOP;
END$$;The output of the query above is provided in the screenshot below.
The above output shows the hire date of 15 employees.
Example 4: Over an array
In PostgreSQL, an array is a collection of data of the same type; this array can contain strings, numbers, dates, and other data types, but only of one kind.
We can iterate over an array using a loop.
Let’s store integer data in the array and iterate over the array to print the data or elements of the array.
DO $$
DECLARE
array_int int[]:= array[1,2,3,4,5,6,7,8];
var int[];
BEGIN
FOR var IN SELECT array_int
LOOP
RAISE NOTICE '%', var;
END LOOP;
END$$;After executing the above query, I obtained the expected output, as shown in the screenshot below.
The above output shows all the data or values of the array named array_int after iterating over it.
Example 5: Insert data into the table
In PostgreSQL, the loop can be used to insert data into the table. Suppose we want to insert data repeatedly, then we can use a loop.
Let’s create a table named dummy.
CREATE TABLE dummy(id int);The above code will create a new table named ‘dummy’ with one column called ‘id’.
Now, will insert the 10 IDs from 1 to 10 in that table using a loop.
DO $$
BEGIN
FOR r IN 1..10
LOOP
INSERT INTO dummy(id) values(r);
end loop;
end $$;
SELECT * FROM dummy;I obtained the expected output, as shown in the screenshot below.
Example 6: Through an array of strings
In PostgreSQL, a string is a collection of characters; an array can also store a string value.
Let’s iterate over the array containing the strings.
DO $$
DECLARE
array_string varchar[]:= array['truck','car','bike'];
var varchar[];
BEGIN
FOR var IN SELECT array_string
LOOP
RAISE NOTICE '%', var;
END LOOP;
END$$;After executing the above query, I got the expected result as shown in the screenshot below.
Example 7: Through a comma-separated string
In PostgreSQL, we can loop through the comma-separated strings, so we will use the function string_to_array to create a list of strings with each comma-separated.
Let’s create a list of strings and iterate over it.
DO $$
DECLARE
var varchar[];
BEGIN
FOR var IN SELECT string_to_array('one,two,three', ',')
LOOP
RAISE NOTICE '%', var;
END LOOP;
END$$;The expected output of the code above is provided below.
Example 8: Through rows in a table
In PostgreSQL, a row is a record stored linearly; each unit of the row is called a record, or a record is a piece of collected data.
Therefore, the code below will print 10 rows from a table named ’employee’.
DO $$
DECLARE
row employee%rowtype;
BEGIN
FOR row in SELECT * FROM employee LIMIT 10
LOOP
RAISE NOTICE '%', row;
END LOOP;
END
$$;In the above code within the DECLARE section, a variable named row is declared using employee%rowtype, which means the data type of the row variable is like an employee table row.
In BEGIN and END blocks, we are using the FOR loop to iterate over the result set of the SELECT statements. This result set contains information about 10 employees in the form of rows.
With the help of the RAISE NOTICE statement, we are printing each row from the result set.
After executing the above query, I got the expected output as shown in the screenshot below.
Conclusion
PostgreSQL loops provide powerful tools for implementing complex procedural logic within your database. In this tutorial, we have learned about PostgreSQL loops with examples and covered the following topics.
You may like the following PostgreSQL tutorials:
- PostgreSQL function returns a table
- PostgreSQL Joins – How to use
- PostgreSQL: Difference between two timestamps
- PostgreSQL Date Difference Examples
- Create a stored procedure in PostgreSQL
- PostgreSQL list databases
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
