Introduction to PostgreSQL LOOP
The PostgreSQL LOOP is used when we want to execute the same block of code statements several times. The PostgreSQL LOOP continues the LOOP body’s execution until a condition defined in the LOOP evaluates to false. The condition defined in the LOOP is termed as the termination condition, which the PostgreSQL evaluates after the first execution of the condition defined. And then, depending upon the condition’s evaluation result, it executes the block of the LOOP body. If the result of the PostgreSQL LOOP condition is true, it will execute loop body statements; otherwise, it will terminate the loop.
Syntax
Consider the following syntax of the PostgreSQL LOOP:
LOOP
Code_statements;
EXIT WHEN condition;
END LOOP;
How does LOOP work in PostgreSQL?
- The PostgreSQL LOOP evaluates the condition defined in the EXIT WHEN clause to decide whether the loop should be terminated or continued for execution.
- If the condition defined with LOOP’s EXIT WHEN clause evaluates to true, then the body of LOOP or code statements are written inside the LOOP is executed once the execution of the LOOP statement completes, the PostgreSQL LOOP’s EXIT WHEN clause condition gets evaluated again.
- The process defined in the above point continuously goes on until the LOOP’s EXIT WHEN clause condition is evaluated to false.
- If the condition defined with PostgreSQL LOOP’ EXIT WHEN clause evaluates to false, then the loop execution terminates immediately.
Example of implementing LOOP in PostgreSQL
1. We will create a function to calculate the nth element of the Fibonacci series by using the following example:
-- FUNCTION: public."Fib_Series_nth_Element"(integer)
-- DROP FUNCTION public."Fib_Series_nth_Element"(integer);
CREATE OR REPLACE FUNCTION public."Fib_Series_nth_Element"(
n integer)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
counter INTEGER := 0 ;
iIndex INTEGER := 0 ;
jIndex INTEGER := 1 ;
BEGIN
IF (n < 1) THEN
RETURN 0 ;
END IF;
LOOP
EXIT WHEN counter = n ;
counter := counter + 1 ;
SELECT jIndex , iIndex + jIndex INTO iIndex, jIndex ;
END LOOP ;
RETURN iIndex ;
END ;
$BODY$;
Illustrate the following SQL statement and snapshot to understand the result of the above function:
SELECT public."Fib_Series_nth_Element"(
3
)
For the 3rd position, the Fibonacci series will have numbers like 0, 1, 1, 2,…
We will execute the above statement once again, to understand the result.
Illustrate the following SQL statement and snapshot to understand the result of the above function:
SELECT public."Fib_Series_nth_Element"(
5
)
For the 5th position, the Fibonacci series will have numbers like 0, 1, 1, 2, 3, 5,…
2. We will create a function having the name ‘LOOP_SUM_ALL’, which will have a LOOP in the function body.
-- FUNCTION: public."LOOP_SUM_ALL"(integer)
-- DROP FUNCTION public."LOOP_SUM_ALL"(integer);
CREATE OR REPLACE FUNCTION public."LOOP_SUM_ALL"(
n integer)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
counter INTEGER := 1;
sumall INTEGER := 0;
BEGIN
LOOP
EXIT WHEN counter > n;
sumall := sumall + counter;
counter := counter + 1;
END LOOP;
RETURN sumall ;
END ;
$BODY$;
The above function takes input as a number, and then it sums all of the elements starting from 1 to the input number and returns us the result.
Illustrate the following SQL statement and snapshot to understand the result of the above function:
SELECT public."LOOP_SUM_ALL"( 3 )
We will execute one more function call with a different input value; illustrate the following SQL statement and snapshot to understand the result of the above function:
SELECT public."LOOP_SUM_ALL"( 5 )
Advantages
Below are the advantages:
1. We use the loops to perform the repetitive task or execute some statements multiple times.
2. We can solve difficult problems where we need some tasks to perform multiple times by using a LOOP.
3. By using LOOP, we can reduce the number of lines to write code as it executes repeatedly.
4. We can avoid code duplicity by using a loop:
- Consider an example where we want to insert 1000 records in a table then we need 1000 INSERT statements, whereas if we have a loop defined with some termination condition, we can insert 1000 records in a single statement by using a loop containing only one INSERT statement.
Conclusion
We hope from the above article you have understood how to use the LOOP and how the PostgreSQL LOOP works. Also, we have added several examples of PostgreSQL LOOP to understand it in detail.
Recommended Articles
This is a guide to PostgreSQL LOOP. Here we discuss How does LOOP work in PostgreSQL along with the example and advantages. You can also go through our other suggested articles to learn more –
