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 execution of WHILE LOOP body until a condition defined in the WHILE LOOP evaluates to false. The condition defined in the PostgreSQL WHILE LOOP is termed as the termination condition, which actually 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 of the PostgreSQL WHILE LOOP condition is true, it will execute loop body statements.
Syntax
Consider the following syntax:
WHILE [condition]
LOOP
[statements]
END LOOP
Flowchart
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 to implement while loop in PostgreSQL:
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, and then it sums all of the elements starting from 1 to the input number and return 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 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 parameter and will 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 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, which will 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
We hope from the above article you have understood how to use the PostgreSQL WHILE LOOP and how the PostgreSQL WHILE LOOP works. Also, we have added several examples of PostgreSQL WHILE LOOP to understand it in detail.
Recommended Articles
This is a guide to PostgreSQL While Loop. Here we discuss Introduction to PostgreSQL While Loop, Syntax, Flowchart, How does it work, examples with code and output. You can also go through our other related articles to learn more –
