In this comprehensive PostgreSQL tutorial, I’ll walk you through everything you need to know about PostgreSQL For loop, from basic concepts to advanced techniques that I’ve personally implemented in many applications.
PostgreSQL For Loop
PostgreSQL provides for loop statements to iterate over a range of integers or a result set in a sequence query.
What is a FOR Loop in PostgreSQL?
A FOR loop in PostgreSQL is a control structure that allows you to iterate over a range of values or query results. Unlike standard SQL, which is set-based by nature, PostgreSQL’s procedural language (PL/pgSQL) introduces these imperative programming constructs to handle row-by-row operations when necessary.
FOR loops are particularly useful when you need to:
- Process query results one row at a time
- Iterate through a range of numbers
- Loop through elements in an array
- Perform operations that can’t be expressed in a single SQL statement
Syntax
The syntax to iterate over a range of integers of for loop The statement is given below:
[<< label>>]
for loop_count in [reverse] from ... to [by step] loop statments
end loop [ label];If we analyze the above syntax of the PostgreSQL for loop, then:
- First, the for loop makes an integer variable loop_count, which is accessible inside the loop. By default, the for loop adds a step to the loop count for each iteration. If we use the reverse option, the for loop subtracts the step from the loop count.
- Second, for and to, the expression provides a lower and upper range of values in an iteration. The for loop always evaluates the expression before entering the loop.
- The third, by the keyword that is used in step keywords, it evaluates iteration steps, and it’s by default 1. The for loop evaluates these step expressions once only.
The flow chart of for loop diagram:
Further, we will cover various PostgreSQL for loop example
Example 1: For Loop with Select command
Let me show an example, how to create for loop iteration in the Select Command:
create or replace function fetcher(n integer)
returns void as $$
declare
emp record;
begin
for emp in select first_name
from actor
order by last_name
limit n
loop
raise notice '%', emp.first_name;
end loop;
end;
$$language plpgsql;
Output as "CREATE FUNCTION".
In the above query, we have created a dummy column as emp to show records under the function fetcher With a variable n as an integer value from the table actor. We try to raise the notice of one parameter ‘%’. Once function fetcher() is created, try to refresh it from Functions Tabs to get it.
Once done, run the function() command given below:
select fetcher(5);After executing the above query, I got the expected output as shown in the below screenshot.
Read: PostgreSQL Order By with Examples
Example 2: For Loop with Insert Command
Let me show you a sample example using the insert command to create a table using a for loop:
do $$
begin
for r in 1..100 loop
insert into actor(id) values(r);
end loop;
end;
$$;In the above query, we defined a variable r under begin clause and end clause and for loop. We then use the INSERT statement to insert integer values from 1 to 100 into the id column of the actor table.
select * from actor(1,100);Example 3: For Loop without Function
In PostgreSQL, we can also use a for loop without creating a function. Instead, we will create an anonymous block, and in the block, we will define a for loop.
Example of for loop without a function is as follows:
DO
$do$
DECLARE
m varchar[];
arr varchar[] := array[['REEBOK','$800'],['GUCCI','$8000']];
BEGIN
FOREACH m SLICE 1 IN ARRAY arr
LOOP
RAISE NOTICE 'Market and price(%,%)',m[1], m[2];
END LOOP;
END
$do$In the above code, we first created an anonymous block, within which we created an array variable with some values. After this, we use the FOREACH loop to iterate over the array values. In the loop, we have the RAISE NOTICE statement to print the result.
Read: PostgreSQL Rank function
Example 4: For Loop Variable
You might know about the variable keyword used to define variable names with or without values. Now, let’s understand how to use variables in PostgreSQL for loop.
The sample example of a for loop variable:
do $$
declare
arrow record;
city_name varchar(50);
begin
for arrow in
select a from (values('New York'), ('Chicago'), ('Boston')) s(a)
loop
city_name = arrow.a;
RAISE NOTICE 'City Name:(%)', city_name;
end loop;
end;
$$;After executing the above query, I got the expected output as per the below screenshot.
Read: PostgreSQL length of string
Example 5: For Loop Update
You may be familiar with the UPDATE statement and its syntax in PostgreSQL. So, the update command is used modify data in the table. The update command has an optional returning clause that returns the updated rows.
The syntax of the update statement is given below:
update table_name set
where col1=valu1,
col2=val2,
....,
where condition;The syntax explanation:
- First, specify the name of the table that you want to update by using
updatekeyword. - Second, specify the column name and new values that you want to update by
SETkeyword. If a column does not appear in theSETclause, it will retain its original value. - Third, which rows to update by using
whereclause. - Note: The WHERE clause is optional. If you omit the WHERE clause, it will update all rows simultaneously.
The returning clause returns the updated values of rows.
When the update statement gets executed successfully, it will provide the tag:
UPDATE COUNT;The following example shows a for loop with the update keyword:
create or replace function agg_loop()
returns void AS $$
declare
ids_array integer;
i INTEGER;
begin
select actor_id from actor into ids_array;
foreach i IN ARRAY ids_array
loop
update actor set actor_id =20 where first_name='Mike';
end loop;
end;
$$
LANGUAGE plpgsql;I got the expected output after executing the above query as shown in the below screenshot.
Read: Postgresql Format + 28 Examples
Example 6: Foreach Loop Array
The FOREACH loop in PostgreSQL is quite similar to the for loop in PostgreSQL. But instead of iterating through rows, the FOREACH iterates over the values of an array.
The syntax of using the FOREACH loop array is given below:
[ <<label>> ]
FOREACH target [ SLICE num ] IN ARRAY expression LOOP
statement
END LOOP [ labels ];If SLICE If it is specified as 0, then the loop iterates over every element of an array by evaluating EXPRESSION. The target variable is assigned to each element value in the sequence, and the loop body is executed for each element.
An example of looping elements by the iterating array is given below:
CREATE FUNCTION scan_rows(int[]) RETURNS void AS $$
DECLARE
x int[];
BEGIN
FOREACH x SLICE 1 IN ARRAY $1
LOOP
RAISE NOTICE 'row = %', x;
END LOOP;
END;
$$ LANGUAGE plpgsql;SELECT scan_rows(ARRAY[[1,2],[3,4],[5,6],[7,8]]);
Read: PostgreSQL Rename Column
Example 7: Anonymous Block for Loop
It is generally constructed dynamically and executed only once by a user. It is a sort of complex SQL statement. The syntax of the anonymous block is given below:
[<<label>>]
[declare
declarations]
Begin
Statements
[ EXCEPTION
EXCEPTION HANDLER]
END label;Let’s look at the syntax details:
- Every block has three sections:
- The
DECLAREsection( it’s optional), where you want to declare all variables, like[varchar(20) abc]that we want to use inBEGINblock. - The
BEGINsection (it’s mandatory), where we can perform some action of variables like[a=200]. - The
EXCEPTIONsection (it’s optional), where we can write a handle for errors that occur during runtime.
- The
ENDThe keyword is used to close the block.LABEL:It’s an optional keyword that is used to identify the anonymous block. In case ofEXITstatement, we need to qualify the variable name declared in a block. Remember, theLABELwhich is given beforeDECLARE/ BEGINmatch with afterENDkeyword.
The DO statement executes an anonymous code block in the version of PostgreSQL 9.0:
DO [language lang_name] code;The anonymous block query by using DO Statement:
do $$
declare
arrow record;
begin
for arrow in
select name from (values('New York'), ('Chicago'), ('Boston')) s(name)
loop
RAISE NOTICE 'City Name:(%)', arrow.name;
end loop;
end;
$$;In the above example, first, we have created an anonymous block. Then, we use a FOR loop in the block to iterate over values such as New York, Chicago, etc. Ultimately, we will obtain the following result.
Read: Postgresql now() function
Example 8: For Loop Break
Let me demonstrate the structure of the break statement in for loop in PostgreSQL:
FOR LOOP
IF(some_condition)
BREAK;
END IF;
END LOOP;Note: There is no break statement in Pl/pgsql. It is misleading to it. If you want, raise exception can be used.
Some statements are used against break statements in pl/pgsql:
EXITis used to terminate the loop in PostgreSQLCONTINUEstatement will continue the next iteration of the loop in PostgreSQL.RETURNstatement will exit from the function in PostgreSQL.
Example 9: For Loop Cursor
In PostgreSQL, the cursor allows you to summarize a query and process a single row at a time. Suppose you want to use a cursor and divide a larger set into single parts. If you run the process immediately, you will encounter a memory overflow error.
If you use the function on the cursor, it will return a reference to the cursor. Using a function is the most effective way of returning the largest number of sets in the cursor.
The following diagram will illustrate how to use the cursor in PostgreSQL:
Explanation of the cursor in PostgreSQL is as follows:
- First, declare a cursor
- Second, open a cursor
- Third, fetch rows from the result and set them to the target.
- Fourth, check if there are no more rows to fetch. If it’s yes, go back to step 3; otherwise, go to step 5.
- Fifth, close the cursor
To declare a cursor variable in the declaration section of the block. PostgreSQL provides a new cursor called REFCURSOR to declare a cursor variable.
declare cursor refcursor;The syntax to declare a cursor to bound a query is as follows:
cursor_name [ [no] scroll ] cursor
[( name datatype1,name_datatype2)] for query;First, you need to declare the variable name of the cursor. Next, you type the keyword scroll if you want the cursor to go backward, but if you use no scroll, then the cursor can’t go backward.
Then, you need to type the cursor keyword followed by the parameter by using the name datatype1 for the query. These arguments will be substituted by value when the cursor is opened. You can use any valid SELECT STATEMENT here.
The following example illustrates how to use the cursor:
create or replace function actor_test()
returns setof varchar as $$
declare
curs cursor for select * from actor;
row RECORD;
begin
open curs;
loop
fetch from curs into row;
exit when not found;
return next row.val;
end loop;I got the expected output as shown below.
The cursor encapsulates all columns from the actor table. We are using this cursor in the for loop to get the values.
Read: Postgresql replace + Examples
Example 10: Continue statement with a for loop
The continue statement in PostgreSQL is used to skip the current iteration and move to the next iteration. It is used in speculation with all types of loops, including unconditional loops, for loops, and while loops.
Syntax:
Continue [label] [WHEN boolean-expression];Analyze the syntax:
label– If there is no label present at that time, the next iteration of the loop starts. That’s all; statements remaining in the loop body are skipped, and controls return to the subsequent loop control execution. However, if the label is presented, execution will continue.WHEN condition:It is simply a boolean expression that specifies the condition to skip the current iteration of the loop. It is as simple as if the condition is proper, the current loop iteration is skipped, and if the condition is false, the loop follows standard loop patterns.
The continue statement sample query:
do
$$
declare
-- Initalize of variable cn
cn int=0;
begin loop
-- Increment of value cn by 2
cn=cn+2;
exit when cn=10;
continue when mod(cn,2) =1;
raise notice '%',cn;
end loop;
end;
$$I got the expected output as shown in the below screenshot.
Read: PostgreSQL Update Join
Example 11: For Loop Counter
The following example uses a for loop statement to iterate over ten numbers from 1 to 10 and display each of the iterations:
do
$$
begin
for counter in 1..10 loop
raise notice 'counter:%',loop;
end loop;
end;
$$
Read: PostgreSQL Min With Examples
Example 12: Create a Table with a for Loop
If you want to create a table by using a for loop, then you need to use the CREATE OR REPLACE function or the DO statement. Let me show an example to create a table as given below:
create or replace function rg() returns
integer as $$
declare
v record;
begin
for v in select distinct r from dev loop
create table rg_$v as select de from dev
where rg='$v';
end loop;
select * from dev;
return 1;
end;
$$ language plpgsql;In the above query, we are creating a function. In the function body, we are using a for loop. Furthermore, within the loop, we are using the CREATE TABLE statement to create a table based on the data from the dev table.
Example 13: For Loop Dynamic SQL
The following form of for loop Allows you to execute the dynamic query, and its syntax is shown below:
[<< label>>]
for row in execute query_expression [using query_parameter [, ..]]
loops
statement
end loop [label];In this syntax:
- The sql statement is
query_expression - To pass a parameter in the query,
usingclause is used.
The following block shows how to use for loop statement in dynamic SQL. It has two configuration variables:
do
$$
declare
sort int=1;
re_count int=10;
rec record;
query text;
begin
query ='select title, release_year from film ';
if sort = 1 then
query= query || 'order by title';
else if sort =2 then
query= query|| 'order by release year';
else
raise 'invalid sort type %s',sort;
end if;
query= query || 'limit $1';
for rec in execute query using re_count
loop
raise notice '%-%', rec.release_year,rec.title;
end loop;
end;
$$In the above example, we first create a block. Then, in the block, we made these two variables.
sort:It will sort the films by title, and then sort them by release year.re_count:It represents the number of rows in the film table. We’ll use a clause tofor loop.
Further, using these variables, we created a dynamic SQL statement with a for loop. Ultimately, we will obtain the following result.
Read: Postgresql Having Clause
Example 14: For Loop with Delete Statement
The delete statement in PostgreSQL allows you to delete one or more records from the table. The following query shows the basic syntax of the DELETE statement:
DELETE FROM TABLE_NAME
WHERE CONDITIONS;The syntax explanation is given below:
- First, specify the name of the table from which you want to delete data by using
deletefromKeyword. - Second, use a condition in the
whereclause to specify which row to delete from the table.
Note: The where Clause is optional. If you omit the where clause, the delete statement will delete all rows from the table.
The example of a for loop with a delete statement:
create or replace function agg_loop()
returns void AS $$
declare
ids_array integer;
i INTEGER;
begin
select actor_id from actor into ids_array;
foreach i IN ARRAY ids_array
loop
delete from actor where first_name='Mike';
end loop;
end;
$$
LANGUAGE plpgsql;In the above example, we are deleting a record from the actor table where the first name is Mike.
Read: PostgreSQL while loop
Example 15: For Loop With Exit Statement
The Exit statement in PostgreSQL allows us to terminate a loop, including an unconditional loop like for and while loop. The following is the syntax of the EXIT statement:
exit [label] [when boolean_exp];The label loop refers to the current loop where the exit is in or the loop label of the outer loop. So, the exit statement will terminate the loop if you don’t use the label.
The when boolean_exp a clause is used to specify the condition that terminates the loop. The exit statement will terminate the loop if the boolean expression is true.
The following statements are equivalent:
exit when count >10;The sample example of a for loop break is given below:
do
$$
begin
<<simple_block>>
begin
exit simple_block;
-- for demo purposes
raise notice '%', 'UNSTOPPABLE!';
end;
raise notice '%', 'BITCOIN UNBROKEABLE';
end;
$$After executing the above query, I got the expected output as shown below.
Read: PostgreSQL REGEXP_REPLACE
Example 16: For Loop with exception clause
In PostgreSQL, when an error occurs within a block, it aborts the execution of the block and any surrounding transactions. To recover from a mistake, we use exception clause with begin.. end block.
The syntax of the exception block in PostgreSQL is as follows:
<<label>>
declare
begin
statements;
exception
when condition [or condition...] then
handle_exception;
[when condition [or condition...] then
handle_exception1;]
[when others then
handle_exception2;
]
end;Now, let’s understand the use of an exception within a loop in PostgreSQL. And for this example, consider the following SQL script.
CREATE TABLE City (city_id INT PRIMARY KEY, city_name TEXT);
CREATE FUNCTION merge_city(key INT, name TEXT) RETURNS VOID AS
$$
BEGIN
LOOP
UPDATE City SET city_name = name WHERE city_id = key;
IF found THEN
RETURN;
END IF;
BEGIN
INSERT INTO City(city_id,city_name) VALUES (key, data);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- Do nothing, and loop will try the UPDATE again.
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;In the above script, we are performing the following task.
- First, we have created a table named City. This table has 2 columns: city_id and city_name.
- Next, we have created a function that accepts two inputs and uses those input values to insert data into the City table.
- In the function, first, we use a loop and an IF clause to check the existence of a record. Then, we use the UPDATE statement to update the record with the same ID.
- After this, we use an EXCEPTION within the loop to check for unique IDs for a city. If the ID is not unique, the loop will be encountered again, and a record with the same ID will be updated.
Read: PostgreSQL if else
Example 17: For Loop in List
In PostgreSQL, we don’t have a list data type similar to what we have in Python. But, instead, we can utilize the array data type in PostgreSQL. A single dimension array data type can also be used just like a list in PostgreSQL.
Now, we have already illustrated how to loop through an array of values in the previous section. For the example demonstration, consider the example given in the “PostgreSQL foreach Loop Array” section.
Example 18: For Loop within For Loop
A nested loop is a loop within a loop, an inner loop within the body of the outer one. A single loop is a repeated series of loop blocks.
The main reason is not to use a nested for loop, as it is overkill frequently for what you are trying to do. In many cases, they are a much faster and less wasteful way to achieve goals as you want to accomplish.
The example of a nested for loop is given below:
CREATE FUNCTION "extract_all_titles2" () RETURNS text AS $$
DECLARE
sub_id INTEGER;
text_output TEXT = '';
sub_title TEXT;
row_data customer%ROWTYPE;
BEGIN
FOR i IN 0..15 LOOP
SELECT customer_id first_name FROM customer WHERE id = i;
text_output = text_output || '''' || sub_title || '''';
FOR row_data IN SELECT * FROM customer
WHERE subject_id = i LOOP
text_output := text_output || row_data.title || '''';
END LOOP;
END LOOP;
RETURN text_output;
END;
$$
LANGUAGE plpgsql;
Read: Postgres date range + Examples
Example 19: For Loop with Print
You might have heard about the printing processes like printing out the input as an output on the output screen like [console.log(), system.out.println()] to print it. Same in PostgreSQL, to print, we use raise notice clause in for loop.
The sample example to print by for loop:
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$$;After executing the above query, I got the expected output as shown in the below screenshot.
Read: Postgresql escape single quote
Example 20: For Loop Reverse
The following example uses for loop statement to iterate ten numbers from 1 to 10 and display each iteration below:
do
$$
begin
for counter reverse in 10..1 loop
raise notice 'counter:%',loop;
end loop;
end;
$$
Example 21: For Loop Return Next
There are two types available that allow you to return data from a function : RETURN and RETURN NEXT .
the syntax of return the statement is given below:
return expression;the syntax of return next expression is given below:
return next expression;They don’t return from the function- they add zero or more rows to the function result set. It executes and then continues with the following statement in the pgsql function. As the result is next executed, the resulting setup is built.
RETURN QUERY has a variant RETURN QUERY EXECUTE that specifies the query executed dynamically.
If you declared the function outside the parameters, write just RETURN NEXT with no expression. On each execution process, the current value of output parameters and variables will be executed and saved as a return value as a low result. To create parameters outside the function, a query example is given below with the result:
create or replace function get_all_films()
returns
setof actor as $body$
declare
r actor%rowtype;
begin
for r in
select * from actor where actor_id>1 limit 5
loop
return next r;
end loop;
return;
end
$body$
language plpgsql;select * from get_all_films();
Read: Postgresql function return table
Example 22: For Loop Select Into
The Select into statement in PostgreSQL is used to select data from the database and assign it to a variable. The following example illustrates the syntax of selecting a statement.
select select_listname into
variable_name from table_name;In this syntax, you have placed a variable after into keyword. The select into statement will assign the data returned by select clause to the variable.
Besides selecting data from a table, there are other clauses you can use after selecting commands such as group by, having and join.
Check the following example:
The example of for loop using select into statement is given below:
create or replace function agg_loop()
returns void AS $$
declare
ids_array integer;
i INTEGER;
begin
select actor_id from actor into ids_array;
foreach i IN ARRAY ids_array
loop
update actor set actor_id =20 where first_name='Mike';
end loop;
end;
$$
LANGUAGE plpgsql;
Read: Postgresql difference between two timestamps
Example 23: For Loop Union
In this topic, we’ll learn how to use the PostgreSQL UNION operator to combine the results of a set of multiple queries in a single result set.
The UNION operator combines result sets of more than two or two SELECT statements in a single result set. The following example illustrates the syntax of UNION operators that combine result sets of operators.
SELECT select_list1
FROM table_name1
UNION
SELECT select_list2
FROM table_name2;Now, we can also use the UNION statement in a for loop to combine the results from the multiple tables. The sample example of Postgresql for loop union:
DO $$
DECLARE
name record;
BEGIN
FOR name IN (SELECT country FROM City1 UNION SELECT country FROM City2)
LOOP
RAISE NOTICE '%', name.country;
END LOOP;
END$$;In the above example, we are using the UNION operator to fetch distinct country names from City1 and City2 tables. And then, using for loop, we iterate over the distinct vales and print the result.
From the above image, we can observe that the output will be the United States and Canada.
Performance
The performance of the for loop is better than that of the while loop. The time taken by the for loop is 0.0001, where the while loop takes 0.026 seconds. The main reason the while loop is slow is that it checks conditions after each iteration.
Example of a for loop:
do $$
begin
for count in 1..5 loop
raise notice 'count:%',count;
end loop;
end;
$$
Conclusion
PostgreSQL For Loop offer a powerful tool when you need row-by-row processing, or complex procedural logic in your database applications. In this tutorial, we have explored this concept using multiple examples.
You may also find the following tutorials on PostgreSQL helpful.
- Upper Bound of for Loop cannot be Null
- PostgreSQL Date Difference Examples
- 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.
