How to Use For Loop in PostgreSQL

A database in Postgres can store a gigantic amount of data. Therefore, traversing through the PostgreSQL database to fetch the desired data may take some time. This becomes more hectic when a user has to execute a specific query again and again to achieve a specific purpose. To avoid these complexities, PostgreSQL offers loops that can iterate through the entire table, a range of integers, etc.

Among these loops, the most convenient and frequently used is the “for” loop which will be discussed in this article along with suitable examples.

How to Use For Loop in PostgreSQL

In PostgreSQL, the FOR loop is used for iterating over a range of values or a result set. The below figure shows the working flow of the FOR loop in PostgreSQL:

The figure shows the working flow of the FOR loop in PostgreSQL

Syntax

It works similarly to traditional programming language “for” loops; however, its syntax is a little bit different, as shown below:

[loop]
  FOR <varName> in [reverse] from.. to [BY step] LOOP
  body
  END LOOP [loop];

Here:

for is a keyword/statement that executes according to the specified loop definition.
The “varName” represents any valid integer variable. This variable will be accessible within the loop(only).
After each iteration, the loop increments the step by a factor of “1”.
“reverse” is optional, if enabled, the loop decrements the step by 1 after each iteration.
“from..” represents the lower bound/range while “to” indicates the upper bound/range.
The for loop will iterate/traverse according to the specified “from..” and “to” expressions.

The loop will keep working/iterating until the condition is satisfied and will immediately stop when the condition becomes false.

Example 1: Iterating Using For Loop

Let’s use a for loop to iterate and display a statement five times:

DO $$
  BEGIN
  FOR count IN 1..5 LOOP
  RAISE NOTICE 'Welcome to Commandprompt';
  END LOOP;
 END; 
$$

In this code, we use the for loop to iterate over a range “1-5”. Within the loop, we use the “raise notice” statement to display a message of our choice each time the loop iterates. Here is the resultant outcome:

image displays a message of our choice each time the loop iterates

Example 2: Iterating an Array Using For Loop

The below code block shows how to iterate over an array using a for loop:

DO $$
  DECLARE arr TEXT[] = '{"welcome", "to", "commandprompt", ".com"}';
  BEGIN
  FOR count IN 1..4 LOOP
  RAISE NOTICE '==> %', arr[count] ;
  END LOOP;
 END; 
$$

In this code, we create a TEXT array that contains four elements. After this, we iterate over the given array via a for loop and display all array elements using a “RAISE NOTICE” statement:

Example 3: Iterating For Loop in Reverse Order

Specifying the “REVERSE” keyword in for loop’s definition allows us to iterate in reverse order:

DO $$
  DECLARE arr TEXT[] = '{"welcome", "to", "commandprompt", ".com"}';
  BEGIN
  FOR count IN REVERSE 4..1 LOOP
  RAISE NOTICE '==> %', arr[count];
  END LOOP;
 END; 
$$

In this code, we use the for loop to iterate over the given array in reverse order. The loop starts iterating from the 4th index and keeps going until it reaches the first element:

Example 4: Iterating For Loop With Customized Steps

Postgres allows us to customize the steps in the for loop according to our needs. For this purpose, simply specify the “step” using the “BY” keyword, as demonstrated below:

DO $$
  BEGIN
  FOR count IN 0..20 BY 5 LOOP
  RAISE NOTICE 'count %', count;
  END LOOP;
 END; 
$$

In this code, we specify the step size as “5”, which means in each iteration, the loop increments by a factor of “5”:

Example 5: Iterating For Loop Over a Result Set

Postgres also allows us to iterate a for loop over a result set. This practice allows us to iterate and fetch the desired records from a result set according to our needs. For instance, the below snippet shows the data of a “product_info” table:

Now in the following code snippet, first, we declare a RECORD-type variable named “availability”:

DO
   $$
 DECLARE availability RECORD;
 BEGIN
 FOR availability IN SELECT pro_name, is_available 
 FROM product_info 
 LIMIT 5
 LOOP 
 RAISE NOTICE '% %', availability.pro_name, availability.is_available;
  END LOOP;
 END;
   $$

We use the for loop to fetch the availability status of the top five products. We utilize the “RAISE NOTICE” statement to show the name and availability of the fetched products:

Example 6: Iterating a Nested For Loop

Let’s learn how to use the nested for loop in Postgres using the following code example:

DO $$
 DECLARE
  emp TEXT[][] := '{{"Joseph",   "John", "Alex"}, {"Mike", "Henry",   "Matt"}, {"Roman", "Ambrose",   "Dean"}}';
 BEGIN
  FOR i IN 1..3 LOOP
  FOR j IN 1..3 LOOP
  RAISE NOTICE 'Emp At [%,%]: %', i, j,   emp[i][j];
  END LOOP;
  END LOOP;
 END 
$$;

In this code, we are given a 2-D array of type text. We execute the for-loop in a nested structure to iterate and print each element of the given multi-dimensional array:

That’s all about using a for loop in PostgreSQL.

Final Thoughts

The for loop in Postgres is used to iterate/traverse over a specific range or a result set. You can use and customize it according to your requirements. For instance, you can iterate a loop in reverse order, set/change the step size, loop through only specific records of a result set, etc. Also, you can use the for loop in a nested structure to iterate over multidimensional or complicated data structures. This post has discussed all of the mentioned use cases with appropriate examples.

How to Use For Loop in PostgreSQL