Updated May 9, 2023
Introduction to PostgreSQL While Loop
The PostgreSQL WHILE LOOP is used when we want to execute the same block of code statements several times. This continues the execution of the WHILE LOOP body until a condition defined in the WHILE LOOP evaluates to false. The condition defined in the PostgreSQL WHILE LOOP is termed the termination condition, which the PostgreSQL evaluates first. And then, depending upon the condition’s evaluation result, it executes the block of the while loop body. If the result condition is true, it will execute loop body statements.
Syntax
Consider the following syntax:
WHILE [condition]
LOOP
[statements]
END LOOPFlowchart
Consider the following flowchart in order to understand the execution flow:
How does While Loop work in PostgreSQL?
The PostgreSQL WHILE LOOP evaluates the condition defined to decide whether the loop should be terminated or continued for execution. If the condition defined with PostgreSQL WHILE LOOP evaluates to true, then the body of WHILE LOOP or code statements are written inside the PostgreSQL WHILE LOOP is executed. Once the execution of the body completes, the PostgreSQL WHILE LOOP condition gets evaluated again. The process defined in the above point continuously goes on until the loop condition is evaluated as false. If the condition defined with PostgreSQL WHILE LOOP evaluates to false, then the loop execution terminates immediately.
Example to Implement While Loop in PostgreSQL
Below are the different examples:
Example #1
We will create a function having the name ‘WHILE_LOOP_SUM_ALL’, which will have a while loop in the function body.
Code:
-- FUNCTION: public."WHILE_LOOP_SUM_ALL"(integer)
-- DROP FUNCTION public."WHILE_LOOP_SUM_ALL"(integer);
CREATE OR REPLACE FUNCTION public."WHILE_LOOP_SUM_ALL"(
n integer)
RETURNS numeric
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
counter INTEGER := 1 ;
sumall INTEGER := 0 ;
BEGIN
WHILE counter <= n LOOP
sumall := sumall + counter ;
counter := counter+1;
END LOOP ;
RETURN sumall ;
END ;$BODY$;Output:
SELECT public."WHILE_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."WHILE_LOOP_SUM_ALL"( 5 )
Explanation: The above function takes input as a number, then sums all of the elements starting from 1 to the input number and returns us the result.
Example #2
We will create one more function of the name CREATE_INSERT_TABLE_WHILE_LOOP with WHILE LOOP, which will actually create a table of the name “LOOP_TABLE” and insert some data into it. The following code will create a function:
Code:
-- FUNCTION: public."CREATE_INSERT_TABLE_WHILE_LOOP"(integer)
-- DROP FUNCTION public."CREATE_INSERT_TABLE_WHILE_LOOP"(integer);
CREATE OR REPLACE FUNCTION public."CREATE_INSERT_TABLE_WHILE_LOOP"(
n integer)
RETURNS boolean
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
counter INTEGER := 1 ;
done boolean := false ;
BEGIN
EXECUTE format('
CREATE TABLE IF NOT EXISTS LOOP_TABLE (
id int
)');
WHILE counter <= n LOOP
done := true;
insert into LOOP_TABLE(id) values (counter);
counter := counter+1;
END LOOP ;
RETURN done ;
END ;
$BODY$;Output:
Illustrate the following SQL statement and snapshot to understand the result of the above function:
SELECT public."CREATE_INSERT_TABLE_WHILE_LOOP"(
3
)
Now we will verify whether the function has created a table ‘LOOP_TABLE’ if it does not exist or not, be as follows:
Illustrate the following SQL statement and snapshot to understand the result of the above function for the table creation:
SELECT id FROM loop_table;
We will execute the function one more time with different parameters and see the result of the function and values of the ‘LOOP_TABLE.’
Illustrate the following SQL statement and snapshot to understand the result of the above function:
SELECT public."CREATE_INSERT_TABLE_WHILE_LOOP"(
5
)
Now we will verify whether the function has created a table ‘LOOP_TABLE’ if it does not exist or not, be as follows:
Illustrate the following SQL statement and snapshot to understand the result of the above function for the table creation:
SELECT id FROM loop_table;
Example #3
We will create one more function to delete rows from LOOP_TABLE as per the input number passed to the function. This function starts from 1 up to the input number and deletes all records.
Code:
-- FUNCTION: public."DELETE_TABLE_WHILE_LOOP"(integer)
-- DROP FUNCTION public."DELETE_TABLE_WHILE_LOOP"(integer);
CREATE OR REPLACE FUNCTION public."DELETE_TABLE_WHILE_LOOP"(
n integer)
RETURNS boolean
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$DECLARE
counter INTEGER := 1 ;
done boolean := false ;
BEGIN
WHILE counter <= n LOOP
done := true;
DELETE FROM LOOP_TABLE WHERE id = counter;
counter := counter+1;
END LOOP ;
RETURN done ;
END ;$BODY$;Output:
Now we will execute the above statement by using the following function call.
Before executing the function, Illustrate the content of the LOOP_TABLE by using the following SQL statement and snapshot:
Select * from LOOP_TABLE;
Illustrate the following SQL statement and snapshot to understand the result of the above function:
SELECT public."DELETE_TABLE_WHILE_LOOP"(
4
)
Illustrate the content of the LOOP_TABLE by using the following SQL statement and snapshot:
Select * from LOOP_TABLE;
Conclusion
From the above article, we hope you understand how to use PostgreSQL WHILE LOOP and how PostgreSQL WHILE LOOP works. Also, we have added several examples of PostgreSQL WHILE LOOP to understand it in detail.
Recommended Articles
We hope that this EDUCBA information on “PostgreSQL While Loop” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
