Loop in PostgreSQL: Syntax & Operations Simplified with Examples

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.

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

You can also take a look at how you can work with PostgreSQL UNION & UNION ALL operators to seamlessly work with your Postgres data.

Use Cases of Loops in PostgreSQL

• Generating Test Data: Create sample data for development or testing by iterating over a range and inserting synthetic records.
• Bulk Data Processing: Efficiently process large datasets by iterating through records in tables, especially for complex operations or transformations.
• Automating Repeated Tasks: Perform repetitive tasks like updating, deleting, or inserting rows based on conditions without manual intervention.
• Dynamic Query Execution: Execute dynamically generated queries within a loop to handle varying input or table structures.
• Data Validation and Auditing: Use loops to check data integrity or validate conditions across rows in real-time.

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. 

Try Hevo and see the magic for yourself. Sign up for a free 14-day trial to streamline your data integration process. You may examine Hevo’s pricing plans and decide on the best plan for your business needs.

Frequently Asked Questions