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

on Database Management Systems, PostgreSQL • July 19th, 2022 • Write for Hevo

Loop in PostgreSQL- Featured Image

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

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?

Рекурсия и цикл, в чем разница? На примере Python | by Evgeny Vladimirovich  | NOP::Nuances of Programming | Medium

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.

Scale your data integration effortlessly with Hevo’s Fault-Tolerant No Code Data Pipeline

As the ability of businesses to collect data explodes, data teams have a crucial role in fueling data-driven decisions. Yet, they struggle to consolidate the scattered data in their warehouse to build a single source of truth. Broken pipelines, data quality issues, bugs and errors, and lack of control and visibility over the data flow make data integration a nightmare.

1000+ data teams rely on Hevo’s Data Pipeline Platform to integrate data from over 150+ sources in a matter of minutes. Billions of data events from sources as varied as SaaS apps, Databases, File Storage, and Streaming sources can be replicated in near real-time with Hevo’s fault-tolerant architecture. What’s more – Hevo puts complete control in the hands of data teams with intuitive dashboards for pipeline monitoring, auto-schema management, and custom ingestion/loading schedules. 

All of this, combined with transparent pricing and 24×7 support, makes us the most loved data pipeline software on review sites.

Take our 14-day free trial to experience a better way to manage data pipelines.

Get started for Free with Hevo!

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.
Loop in PostgreSQL | flowchart

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.

No-code Data Pipeline For PostgreSQL