PostgreSQL - Loop Statement

The LOOP statement in PL/pgSQL is used to create an unconditional loop that executes a block of code repeatedly until a RETURN or EXIT statement terminates it. This article will help you understand the syntax and usage of the LOOP statement, and provide examples to display its application.

Let us get a better understanding of the Loop Statement in PostgreSQL from this article.

Syntax

<<label>>
loop
  statements/body;
end loop;

In the above syntax, we must ideally do the following :

• Condition for termination:  We should try to define a condition in the body of the loop until which our loop runs. As soon as this condition fails, the loop will be terminated. If we do not define a termination condition, the loop executes infinite times and we encounter the case of an infinite loop.
   
• Increment/Decrement: We should try to include an increment or decrement statement inside the body to increase or decrease the running variable. If we do not change the value of the variable, the loop gets stuck in the same condition again and again and we encounter the case of an infinite loop.

Loop Termination

To terminate the running of the loop, we can simply include an 'if' statement with an 'exit' statement with the following syntax:

<<label>>
loop
  statements;
  if condition then
     exit;
  end if;
end loop;

Nested Loops

A condition when we place a loop inside another loop is called a nested loop. It is important to note that whenever we use nested looping, we must define the loop labels in the exit or continue statements to show precisely which loop we are referring to.

<<outer>>
loop  
  statements;
  <<inner>>
  loop
    inside statements;
    exit <<inner>>
  end loop;
end loop;

PostgreSQL Loop Statement Examples

Let us take a look at some of the examples of Loop Statement in PostgreSQL to better understand the concept.

Example 1: Counting Up from 1 to 5

The following example shows how to use the loop statement to print all numbers from 1 to 5.

Query:

do $$
declare
  n integer:= 6;
  cnt integer := 1 ;  
begin
loop  
 exit when cnt = n ;
 raise notice '%', cnt;  
 cnt := cnt + 1 ;  
end loop;  
end; $$;

Output:

Explanation: In this example, the 'cnt' variable is incremented in each iteration. The value of 'cnt' is printed until it reaches the value of 'n', at which point the loop terminates.

Example 2: Counting Down from 10 to 1

The following example shows how to use the loop statement to print all numbers from 10 to 1.

Query:

do $$
declare
 n integer:= 0;
 cnt integer := 10 ;  
begin
loop  
exit when cnt = n ;
raise notice '%', cnt;  
cnt := cnt - 1 ;  
end loop;  
end; $$;

Output:

Explanation: In the above example, we define a 'cnt' variable whose value is decreased at each iteration. The value of 'cnt' is printed until it reaches our minimum value of 'n' after which the loop is terminated.

Conclusion

The LOOP statement in PL/pgSQL is a powerful tool for repeating a block of code until a specific condition is met. By understanding its syntax and usage, you can efficiently perform repetitive tasks, iterate through records, and handle complex calculations. Remember to always define a termination condition to avoid infinite loops, and use increment or decrement operations to ensure the loop progresses towards its end.