EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL While Loop
 

PostgreSQL While Loop

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 9, 2023

PostgreSQL While Loop

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax

Consider the following syntax:

WHILE [condition]
LOOP
[statements]
END LOOP

Flowchart

Consider the following flowchart in order to understand the execution flow:

WHILE condition FLOWCHART

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 )

While Loop Sum All

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 )

PostgreSQL While Loop 2

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
)

Insert Table

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;

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
)

PostgreSQL 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;

Table Creation

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;

 

PostgreSQL While Loop 7

Illustrate the following SQL statement and snapshot to understand the result of the above function:

SELECT public."DELETE_TABLE_WHILE_LOOP"(
4
)

Delete Table

Illustrate the content of the LOOP_TABLE by using the following SQL statement and snapshot:

Select * from LOOP_TABLE;

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.

  1. PostgreSQL Timestamp
  2. Guide to PostgreSQL Functions
  3. Top 9 Parameters of PostgreSQL log
  4. Learn the PostgreSQL cluster

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW