Loop in PostgreSQL: Syntax & Operations Simplified | A 101 Guide

Are you still stuck finding a basic tutorial on Loop in PostgreSQL?

Tables in PostgreSQL can be used to store data in various ways. We utilize several looping and conditional expressions to retrieve data and perform multiple operations. We require a looping technique to iterate through the stored data. 

Many conditional and looping commands are available in the PostgreSQL database. Your hustle of finding solutions on loop in PostgreSQL ends here.

This blog will teach you about looping, the different types of looping statements, and how you can utilize the loop in PostgreSQL to fulfill your use case.

Table of Contents

• Prerequisites
• What Is Looping?
• What are the Types of Loop in PostgreSQL?
  • The PostgreSQL Loop
    • Syntax
    • Example & Output
  • The PostgreSQL For Loop
    • Syntax
    • Example & Output
• For Loop Diagram Flowchart
• For Looping Reverse Example
• For Looping Through Arrays
• For Looping Through Query Results
• Conclusion

Prerequisites

• Basic knowledge of Postgres SQL statements such as SELECT, FROM, UPDATE, and INSERT.
• Basic understanding of PostgreSQL Arrays.
• Basic Experience with Loops

No worries if the term “loop” was never acquainted with you. We have discussed the necessary introduction to loop in the article.

What Is Looping?

We frequently find ourselves in situations where we must repeatedly repeat a task. This is known as looping through statements. We can loop the statements for a set number of times or until our criterion is met.

Loops, like conditional statements, are another method of managing the flow of functions. Iteration is often used in loops to achieve various tasks, and iteration can considerably enhance the capabilities of a PL/pgSQL procedure.

PL/pgSQL has three iterative loops: 

• The basic loop
• The slightly more complex WHILE loop
•  The FOR loop. 

The FOR loop is the most commonly used of the three since it can be used in many programmed circumstances, while the other loops are equally helpful.

What are the Types of Loop in PostgreSQL?

To begin our tutorial on PostgreSQL loop types, we will discuss the most basic loop type available in Postgres, which requires no conditions.

The PostgreSQL Loop

The basic loop structure has a set of statements between the LOOP and END LOOP lines. The statements are executed with each iteration, and control returns to the top of the loop. The Postgres Loop with no conditions is terminated with an EXIT WHEN statement. 

Below is the syntax of the Basic loop in PostgreSQL:

Syntax

LOOP
    [statements];
    EXIT WHEN [condition met]
END LOOP;

Example & Output

count := 0
LOOP
    RAISE NOTICE count;
    count := count + 1;
    EXIT WHEN count = 5;
END LOOP;

Output:

0
1
2
3
4
5

The PostgreSQL For Loop

Postgresql supports For loop statements to iterate through a range of integers or results from a sequence query.
The For loop is used to iterate over a set of numbers or objects.

Syntax

FOR [loop_counter name] IN [REVERSE] [START] .. [END] [BY stepping]
LOOP
    [statements];
END LOOP;

In the above syntax,

• loop_counter_name represents an integer variable that is created at first. The for loop increments the loop_counter_name  after each iteration. 
• START and END signify the lower and upper bound of the range.
• The iteration step is specified in the stepping that precedes the BY keyword, with 1 as the default value. 
• The LOOP keyword denotes the start of the for loop’s body, which will be run each time the loop is iterated.
• The lines of code we wish to execute repeatedly are put in the [statements] section, 
• END LOOP indicates the end of the for loop’s execution. 
• REVERSE is an optional argument that, when supplied, causes the counting variable to be decremented instead of incremented each time the iteration is completed.

Example & Output

We’ll demonstrate looping over integers as well as looping through other various types. The Postgres For Loop has the most significant possibilities of any PostgreSQL loop type.

BEGIN
    FOR counter IN 10 .. 20 BY 2
    LOOP
        RAISE NOTICE 'Number Count: %', counter;
    END LOOP;
END;

In the above code:

•  counter: A variable used to track and store the current position we are at in the for a loop.
• 10 .. 20: Our loop’s range (start and end value).
• BY 2: The “stepping”; how many values to skip at each iteration through the loop.
• LOOP: This keyword represents the beginning of our For loop.
• END LOOP: The keyword signifies the end of our For loop.

Output:

NOTICE: Number Count: 10
NOTICE: Number Count: 12
NOTICE: Number Count: 14
NOTICE: Number Count: 16
NOTICE: Number Count: 18
NOTICE: Number Count: 20

For Loop Diagram Flowchart

• The PostgreSQL FOR LOOP flowchart starts with the declared counter variable used in FOR LOOP body.
• Then evaluates the condition defined to decide whether the loop should be terminated or continued for execution. If the condition defined with PostgreSQL FOR LOOP evaluates to true, then the body of FOR LOOP or code statements written inside the  PostgreSQL FOR LOOP is executed.
• Once the execution of the body completes, the PostgreSQL FOR LOOP condition gets re-evaluated.
• The process defined in the above point continues until the loop condition is evaluated as false. If the condition defined with PostgreSQL FOR LOOP evaluates to false, then the loop execution terminates immediately.

For Looping Reverse Example

for loop_counter in reverse 10..5 loop
      raise notice 'counter: %', loop_counter;
   end loop;

Output:

NOTICE:  Counter: 10
NOTICE:  Counter: 9
NOTICE:  Counter: 8
NOTICE:  Counter: 7
NOTICE:  Counter: 6
NOTICE:  Counter: 5

For Looping Through Arrays

The array in Postgresql is a collection of data of the same kind; an array can include strings, integers, dates, and so on, but only of one data type.

Using the loop, we can iterate through an array.

Let’s put integer data in an array and loop through it to display the data or elements of the array.

DO $$
DECLARE
array_int int[]:= array[11,22,33,44,55,66,77,88];
var int[];
BEGIN
FOR var IN SELECT array_int
	LOOP
	RAISE NOTICE '%', var;
	END LOOP;
END$$;

Output:

NOTICE:  {11,22,33,44,55,66,77,88}

For Looping Through Query Results

You can iterate through the query results and change the data using a different form of FOR loop. The syntax is as follows:

[ <<label>> ]
FOR target IN query LOOP
    statements
END LOOP [ label ];

To begin, we build a sample table and use the following commands to execute examples:

CREATE TABLE movies (
  movie_id serial PRIMARY KEY,
  movie_name VARCHAR NOT NULL,
  movie_duration INT
);

Then we enter the following information into our employee table:

INSERT INTO movies(
  movie_id,
  movie_name,
  movie_duration
)
VALUES
  (1, 'Homecoming', 185),
  (2, 'Harry Potter', 175),
  (3, 'Train to Busan', 190),
  (4, 'The Notebook', 149),
  (5, 'Frozen', 120);

Example:

The following code utilizes the for loop statement to iterate over the largest duration 3 movies_duration:

do
$$
declare
    ele record;
begin
    for ele in select movie_id, movie_name 
           from movies
           order by movie_duration desc
           limit 3 
    loop 
    raise notice '% - % ', ele.movie_id, ele.movie_name;
    end loop;
end;
$$;

Conclusion

This article demonstrated using instructions and examples to utilize the Loop in PostgreSQL. The blog offered an overview of the Loop in PostgreSQL, Types of the loop in PostgreSQL. The lesson then demonstrated the loop’s syntax in PostgreSQL, along with examples.

Hopefully, you will try leveraging the Loop in PostgreSQL.
Feel free to comment below expressing your thoughts or recommendations on Loop in PostgreSQL.

If you are a PostgreSQL user, replicating data into a warehouse using ETL for Data Analysis can get demanding. This problem is exaggerated because they need extensive money and resources to hire data engineers and analysts to make sense of this data. 

Luckily, you can set up and start Data Replication from PostgreSQL to your favorite warehouse in a matter of minutes using Hevo. 

Hevo Data, with its strong integration with 100+ Data Sources such as PostgreSQL, MySQL, and MS SQL Server, allows you to not only export data from sources & load data to the destinations but also transform & enrich your data & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. 

Visit our Website to Explore Hevo

Hevo lets you replicate your data from your PostgreSQL database to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt within minutes with just a few clicks.

Give Hevo a try. Sign Up here for a 14-day full feature access trial and experience the feature-rich Hevo suite first hand. You can also check our unbeatable pricing and decide on your best-suited plan. 

Share your thoughts on learning about Loop in PostgreSQL in the comments below. If you have any questions, do let us know. We’d be happy to help.