PostgreSQL Loop Examples

In this PostgreSQL tutorial, we will learn about the “Postgresql loop” for inserting records or accessing an array using a loop and cover the following topics.

• Postgresql loop through query results
• Postgresql loop through date range
• Postgresql loop through records
• Postgresql loop over array
• Postgresql loop insert
• Postgresql loop through array of strings
• Postgresql loop through comma separated string
• Postgresql loop through rows in table

Postgresql loop

In Postgresql, we can use a loop to perform the same task again and again or repeat a series of statements.

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 LOOP.

Here, we will loop through a table named major_cities that contains the population of major cities in the United Kindom.

DO $$
DECLARE
city_names varchar;
BEGIN
FOR city_names IN SELECT city_name FROM major_cities
	LOOP
	RAISE NOTICE '%', city_names;
	END LOOP;
END$$;

• Postgresql if else

Postgresql loop through query results

In Postgresql, whenever we perform a query it returns some result set, the result set can be any table containing records or arrays or a list of integers. So when we want to iterate or loop through these result sets, we can use the for a 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 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, the firstname is a variable of varchar type 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 block, we are looping through the firstname variable to get the first_name of the employees using the LOOP and END LOOP blocks.

The output of the above code is given below.

This is an example of a Postgresql loop through query results.

• Postgres date range

Postgresql loop 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$$;

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 of 10 employees using LIMIT 10.

In the BEGIN and END block, we are looping through emp_name for first_name and last_name of the employee using the LOOP and END LOOP blocks.

The output of the above code is given below.

In the above output, it shows the full name of 10 employees by looping through query results.

This is an example of loop through records from a table in PostgreSQL.

• Drop Database PSQL

Postgresql loop 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.

Think for a minute that we have a date column and we want to print some range of dates, this all can be done with the help of a loop.

Here we are going to use the same employee table that we have used in the above sub-topic.

Let’s print the hire date of the employees from 1991-06-01 to 1995-10-10 using the below code.

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 above code is given below.

In the above output, it shows the hire date of 15 employees.

This is an example of loop through date range in PostgreSQL.

• PostgreSQL Update

Postgresql loop over array

In Postgresql, the array is a collection of data of the same type, this array can contain strings, numbers, and dates, etc. but only of one data type.

We can iterate over an array using the loop.

Let’s store integer data in the array and iterate over the array to print the data or element 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$$;

The output of the above code is given below.

In the above output, it shows all the data or value of the array named array_int after iterating over it.

• Postgresql date comparison

Postgresql loop insert

In Postgresql, the loop can be used to insert data into the table, suppose we want to insert some kind of data, again and again, then we can use the loop.

Let’ create a table named dummy.

CREATE TABLE dummy(id int);

The above code will create a new table named dummy with one column named id.

Now, will insert the 10 ids from 1 to 10 in that table using the loop.

DO $$
BEGIN
FOR r IN 1..10 
	LOOP
	INSERT INTO dummy(id) values(r);
	end loop;
end $$;

SELECT * FROM dummy;

The output of the above code is given below.

This is an example of Postgresql loop insert.

• Postgresql date to string

Postgresql loop through array of strings

In Postgresql, the string is a collection character, an array can also store the 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$$;

The output of the above code is given below.

This is an example of Postgresql loop through array of strings.

• Postgresql escape single quote

Postgresql loop through comma separated string

In Postgresql, we can loop through the comma-separated strings, so we will use the function string_to_array to create the list of strings with comma-separated each.

Let’s create the list of strings and iterate or loop 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 output of the above code is given below.

This is how to loop through a comma-separated string in PostgreSQL.

• PostgreSQL list users

Postgresql loop through rows in table

In Postgresql, the row is a record stored in a linear way, each unit of the row is called a record or record is a piece of collected data.

So the below code 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 DECLARE section, a variable named row is declared using employee%rowtype, which means the data type of row variable is like 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 of 10 employees in the form of rows.

With the help of the RAISE NOTICE statement, we are printing each row from the result set.

The output of the above code is given below.

This is how to loop through rows in a table in PostgreSQL.

You may like the following PostgreSQL tutorials:

• Postgresql function return table
• Postgresql Joins – How to use
• Postgresql difference between two timestamps
• PostgreSQL Date Difference Examples
• Create a stored procedure in PostgreSQL
• PostgreSQL list databases

So in this tutorial, we have learned about the Postgresql loop with examples and covered the following topics.

• Postgresql loop through query results
• Postgresql loop through date range
• Postgresql loop through records
• Postgresql loop over array
• Postgresql loop insert
• Postgresql loop through array of strings
• Postgresql loop exit when not found
• Postgresql loop through comma separated string
• Postgresql loop through rows in table