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.
