PL/pgSQL While Loop

Summary: in this tutorial, you will learn how to use PL/pgSQL while loop statement to execute statements as long as a condition is true.

Introduction to PL/pgSQL while loop statement

The while loop statement executes one or more statements as long as a specified condition is true.

Here’s the basic syntax of a while loop statement:

[ <<label>> ]
while condition loop
   statements;
end loop;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, PostgreSQL evaluates the condition before executing the statements.

If the condition is true, it executes the statements. After each iteration, the while loop evaluates the codition again.

Inside the body of the while loop, you need to change the some variables to make the condition false or null at some points. Otherwise, you will have an indefinite loop.

Because the while loop tests the condition before executing the statements, it is often referred to as a pretest loop.

The following flowchart illustrates the while loop statement:

PL/pgSQL while loop example

The following example uses the while loop statement to display the value of a counter:

do $$ 

declare 
	counter integer := 0;
begin 
	while counter < 5 loop 
		raise notice 'Counter %', counter;
		counter := counter + 1;
	end loop;
end;

$$;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

NOTICE:  Counter 0
NOTICE:  Counter 1
NOTICE:  Counter 2
NOTICE:  Counter 3
NOTICE:  Counter 4Code language: Shell Session (shell)

How it works.

• First, declare the counter variable and initialize its value to 0.
• Second, use the while loop statement to show the current value of the counter as long as it is less than 5. In each iteration, increase the value of counter by one. After 5 iterations, the counter is 5 therefore the while loop is terminated.

Summary

• Use the PL/pgSQL while loop statement to execute one or more statements as long as long as a specified condition is true.