PostgreSQL provides the loop statement which simply defines an unconditional loop that executes repeatedly a block of code until terminated by an exit or return statement. The while loop statement executes a block of code till the condition remains true and stops executing when the conditions become false.
The syntax of the loop statement:
[ <<label>> ] while condition loop statements; end loop;
If we analyze the above syntax:
- Condition: If the condition is true, it executes the statements. After each iteration, the while loop again checks the condition.
- Body / Statements: Inside the body of the while loop, we need to change the values of some variables to make the condition false or null at some points. Otherwise, we will encounter the case of an infinite loop.
As the while loop tests the condition before executing the statements, it is also referred to as a pretest loop.
The following flowchart describes the while loop statement:
The following code is used to raise notices while increasing the count of an arbitrary variable from 0 to 9.
do $$ declare add integer := 0; begin while add <10 loop raise notice 'Out addition count %', add; add := add+1; end loop; end$$;
The following code is used to raise notices while decreasing the count of an arbitrary variable from 10 to 1.
do $$ declare add integer := 10; begin while add > 0 loop raise notice 'Out addition count %',add; add := add-1; end loop; end$$;