In this PostgreSQL tutorial, we will study the use of PostgreSQL For Loop and we will also cover multiple examples. There are lists of the topic that comes under discussion is given below:
- Postgresql for loop
- Postgresql for loop select
- Postgresql for loop insert
- Postgresql for loop without function
- Postgresql for loop variable
- Postgresql for loop update
- Postgresql foreach loop array
- Postgresql anonymous block for loop
- Postgresql for loop break
- Postgresql upper bound of for loop cannot be null
- Postgresql for loop cursor
- Postgresql for loop continue
- Postgresql for loop counter
- Postgresql for loop create table
- Postgresql for loop dynamic sql
- Postgresql for loop delete
- Postgresql for loop exit
- Postgresql for loop exception
- Postgresql for loop in list
- Postgresql for loop in for loop
- Postgresql for loop performance
- Postgresql for loop print
- Postgresql for loop reverse
- Postgresql for loop return next
- Postgresql for loop select in
- Postgresql for loop union
Postgresql For Loop
Postgresql provides for loop statements to iterate a range of integers or results set in a sequence query.
The syntax to iterate 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 PostgreSQL for loop, then:
- First, the
for
loop makes a integer variableloop_count
which is accessible inside the loop. By default,for loop
addsstep
toloop_count
for each iteration. If we usereverse
option,for loop
substractsstep
fromloop_count.
- Second,
for
andto
expression provides lower and upper range of value in iteration. The for loop always evalutes the expression before entering the loop. - Third,
by
keyowrd which is used instep
keyword, it evaluates iteration steps and it’s by default 1. The for loop evauates this step expressions once only.
The flow chart of for loop
diagram:

Further, we will cover various examples related to PostgreSQL For Loop.
Postgresql for Loop Select
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 this 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 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);
The output will show a message inside Message
column:

Read: PostgreSQL Order By with Examples
Postgresql for Loop Insert
Let me show you a sample example using the insert command to create a table using 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. And then, we are using the INSERT statement to insert integer values from 1 to 100 in the id column of the actor table.
select * from actor(1,100);
Postgresql 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 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, first, we created an anonymous block and in the block, we created an array variable with some values. After this, we are using the FOREACH loop to iterate over the array values. In the loop, we have the RAISE NOTICE statement to print the result.

for loop
without functionRead: Postgresql Rank function
Postgresql for Loop Variable
You might know about the variable keyword which is 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 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;
$$;

Read: Postgresql length of string
Postgresql for Loop Update
You might know about 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
update
keyword. - Second, specify column name and new values that want to update by
SET
keyword. If column do not appear in theSET
clause , it will retains it’s original value. - Third, which rows to update by using
where
clause. - Note: Where clause is optional. if you omit the where clause it will update all rows at the same time.
The returning clause returns the update values of rows.
When the update statement gets executed successfully, it will provide tag:
UPDATE COUNT;
The following example shows for loop with 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;

Read: Postgresql Format + 28 Examples
Postgresql 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
is specified as 0, then loop iterates every element of an array by evaluating EXPRESSION
. The target
variable assigned 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
Postgresql 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 syntax details:
- Every block has 3 sections:
- The
DECLARE
section( it’s optional), where you want to declare all variables like[varchar(20) abc]
that we want to use inBEGIN
block. - The
BEGIN
section (it’s mandatory), where we can perform some action of variable like[a=200].
- The
EXCEPTION
section (it’s optional), where we can write a handle of error that occurs during runtime.
- The
END
the keyword is used to close the block.LABEL:
It’s an optional keyword that is used to identify the anonymous block. In case ofEXIT
statement, we need to qualify the variable name that is declared in a block. Remember, theLABEL
which is given beforeDECLARE/ BEGIN
match with afterEND
keyword.
The DO
the 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. And then, we are using a FOR loop in the block to iterate over some values like New York, Chicago, etc. In the end, we will get the following result.

Read: Postgresql now() function
Postgresql 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 misleading to it. If you want, raise
exception
can be used.
Some statements are used against break statement in pl/pgsql:
EXIT
is used to terminate the loop in PostgreSQLCONTINUE
statement will continues next iteration of the loop in PostgreSQL.RETURN
statement will exit from function in PostgreSQL.
Postgresql Upper Bound of for Loop cannot be Null
In PostgreSQL for loop, there are two bounds: lower bound and upper bound. The lower bound starts from lower values which means from where the loop will start like [0,1,…etc]. And the upper bound is the highest value of the loop where the iteration will end.
Remember that, upper bound can’t be null in PostgreSQL. If the upper bound is provided as null, then it will provide a logic error in the message box.
do $$
begin
for count in 1..null loop
raise notice 'count:%',count;
end loop;
end;
$$

Read: Postgresql unique constraint
Postgresql 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 just divide a larger set into single parts. If you run the process at once you will get a memory overflow error.
If you use the function on the cursor, it will return a reference to the cursor. It is the most effective way of returning the larger number of sets in cursor by using function.
The following diagram will illustrate how to use cursor in Postgresql:

for loop cursor
Explanation of cursor in PostgreSQL is as follows:
- First, declare a cursor
- second, open a cursor
- Third, fetch rows from result and set to target.
- Fourth, check if there is no more rows to fetch. If it’s yes , go back to step third 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 query by the following syntax:
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 a keyword scroll if you want to cursor to go backward but if you use no scroll then the cursor can’t go backward.
Then you need to type cursor keyword followed by parameter by using 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;

The curs is a cursor that encapsulates all columns from the actor table. We are using this cursor in the for loop to get the values.
Read: Postgresql replace + Examples
Postgresql for Loop Continue
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 loop, for loop, and while loop.

Continue
statement in for loop
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, controls return to the next loop control execution. But if the label is presented, the execution will be continued.WHEN condition:
It is simply a boolean expression that specifies the condition to skip the current iteration of the loop. It is as simple that, if the condition is true, the current loop iteration is skipped and if the condition is false, the loop follows normal loop patterns then.
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;
$$

continue
statementRead: PostgreSQL Update Join
Postgresql for Loop Counter
The following example uses for loop statement to iterate the 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;
$$

counter
by for loop
in PostgreSQLRead: PostgreSQL Min With Examples
Postgresql for Loop Create Table
If you want to create a table by using for loop then you need to use CREATE OR REPLACE function or 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. Further, in the loop, we are using CREATE TABLE statement to create a table based upon the data of the dev table.

for loop
to create table Postgresql for Loop Dynamic Sql
The following form of for
loop
allows you to execute the dynamic query and its syntax is shown given 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
- In order to pass parameter in the query,
using
clause is used.
The following block shows how to use for loop
statement in dynamic SQL
. It has two configurations 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, first, we have created a block. Then, in the block, we created these two variables.
sort:
it will sort the film by title and 2 sort film by release year.re_count:
it represents to number of rows to thefilm
table. We’ ll use using clause tofor loop.
Further, using these variables, we created a dynamic SQL statement with the for loop. In the end, we will the result as follows.

for loop
with dynamic sql
Read: Postgresql Having Clause
Postgresql for Loop Delete
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
delete
from
keyword. - Second, use a condition in the
where
clause to specify from which row delete from 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 for loop with 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.

for loop
with delete
statementRead: Postgresql while loop
Postgresql for Loop Exit
The Exit statement in PostgreSQL allows us to terminate a loop including unconditional loop like for
and while
loop. The following 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 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;
$$

Read: Postgresql REGEXP_REPLACE
Postgresql for Loop Exception
In PostgreSQL, when an error occurs in a block, it will abort the execution of the block and surrounding transactions. To recover from an error, 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 with the name City. This table has 2 columns, city_id, and city_name.
- Next, we have created a function that will accept 2 inputs and use those input values to insert values in the City table.
- In the function, first, we are using a loop and IF clause to check the existence of a record. And then, useing the UPDATE statemen to update the rcord with same id.
- After this, we are using EXCEPTION within the loop to check for unique IDs for a city. And if the id is not unique then loop will encountered again and record with same id will be updated.

Read: Postgresql if else
Postgresql 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.
Postgresql for Loop in 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 repeated series of loop blocks.
The main reason is not to use nested for loop that it 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 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
Postgresql for Loop Performance
The performance of for loop is better than the while loop. The time taken by 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 for loop:
do $$
begin
for count in 1..5 loop
raise notice 'count:%',count;
end loop;
end;
$$

Postgresql for Loop 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$$;

Read: Postgresql escape single quote
Postgresql 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;
$$

reverse counter
by for loop
in PostgreSQLPostgresql for Loop Return Next
There are two types available that allow you to return data from 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 just simply add zero or more rows to the function result set. It executes and then continues with the next statement in the pgsql function. As the result next is executed, the resulting setup is built it.
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, variables will be executed and save a return value as a low result. To create parameters outside 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();

return next
expression in for loop
PostgresqlRead: Postgresql function return table
Postgresql for Loop Select Into
The Select into
statement in PostgreSQL is used to select data from the database and assign it to the variable. The following example illustrates the syntax of select into 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 like 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;

for
loop with select into
statementRead: Postgresql difference between two timestamps
Postgresql for Loop Union
In this topic, we’ll learn how to use the PostgreSQL UNION
operator to combine 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 combines 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 we will get output as the United States and Canada.
You may also like to read the following tutorials on PostgreSQL.
So, in this tutorial, we have studied the use of PostgreSQL For Loop using multiple examples. There are lists of the topic that comes under discussion is given below:
- Postgresql for loop
- Postgresql for loop select
- Postgresql for loop insert
- Postgresql for loop without function
- Postgresql for loop variable
- Postgresql for loop update
- Postgresql foreach loop array
- Postgresql anonymous block for loop
- Postgresql for loop break
- Postgresql upper bound of for loop cannot be null
- Postgresql for loop cursor
- Postgresql for loop continue
- Postgresql for loop counter
- Postgresql for loop create table
- Postgresql for loop dynamic sql
- Postgresql for loop delete
- Postgresql for loop exit
- Postgresql for loop exception
- Postgresql for loop in list
- Postgresql for loop in for loop
- Postgresql for loop performance
- Postgresql for loop print
- Postgresql for loop reverse
- Postgresql for loop return next
- Postgresql for loop select in
- Postgresql for loop union
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.