Postgresql For Loop + Examples

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 variable loop_count which is accessible inside the loop. By default, for loop adds step to loop_count for each iteration. If we use reverse option, for loop substracts step from loop_count.
• Second, for and to 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 in step 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.

Read: 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 the SET 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 in BEGIN 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.
• 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 of EXIT statement, we need to qualify the variable name that is declared in a block. Remember, the LABEL which is given before DECLARE/ BEGIN match with after END 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 PostgreSQL
• CONTINUE 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:

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.

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

Read: 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;
$$

Read: 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.

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 the film table. We’ ll use using clause to for loop.

Further, using these variables, we created a dynamic SQL statement with the for loop. In the end, we will the result as follows.

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.

Read: 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;
$$

Postgresql 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();

Read: 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;

Read: 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.

• PostgreSQL Date Difference Examples
• PostgreSQL list databases

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