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 LOOP
 

PostgreSQL LOOP

Sohel Sayyad
Article bySohel Sayyad
EDUCBA
Reviewed byRavi Rathore

Updated May 17, 2023

PostgreSQL LOOP

 

 

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.

Watch our Demo Courses and Videos

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

Syntax

Consider the following syntax:

LOOP
Code_statements;
EXIT WHEN condition;
END LOOP;

How does LOOP work in PostgreSQL?

  1. The PostgreSQL LOOP evaluates the condition defined in the EXIT WHEN clause to decide whether the loop should be terminated or continued for execution.
  2. 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.
  3. The process defined in the above point continues until the LOOP’s EXIT WHEN clause condition is evaluated as false.
  4. 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
)

PostgreSQL LOOP output 1

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
)

 

PostgreSQL LOOP output 2

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 )

output 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 )

output 4

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

From the above article, we hope you understand 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

We hope that this EDUCBA information on “PostgreSQL LOOP” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. PostgreSQL Timestamp
  2. PostgreSQL LIMIT
  3. SQL SELECT RANDOM
  4. ROLLUP in SQL

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