PostgreSQL is used to store data by creating databases in the form of tables and then connecting them using relationships between them. In PostgreSQL, loops are used to iterate over a set of rows/records or run statements repeatedly until the said condition becomes false. Postgres supports various types of loops, one such loop is the “While” loop.

This guide will explain how to use the While loop in PostgreSQL.

How to Use While Loop in PostgreSQL?

While loop runs the query until the condition remains true and it stops execution as soon as the condition is false. The condition will be evaluated during each iteration, and the code specified within the loop's body will be executed as long as the condition remains true.

Syntax

The following is the syntax for the while loop in PostgreSQL:

[ <<label>> ]
 while condition loop
  statements;
 end loop;

Here:

- The loop is executed after checking the condition after each iteration and runs until the condition remains true.
- There should be a false condition in the While loop to terminate the loop, otherwise, the loop will run indefinitely without stopping it.
- After pretesting the condition, the loop runs the statements inside the loop body and again checks the condition for a new value.

Example 1: Iterating Using While Loop

The following code is the basic example of While loop execution which will print the first 10 integers on the screen:

do $$
 declare 
  counter integer := 1;
 begin
  while counter <= 10 loop
  raise notice 'Counter %', counter;
   counter := counter + 1;
  end loop;
 end$$;

The above code will declare a variable and assign it the starting value to start the iterations of the loop. After that, provide the condition which will be checked before running the statements inside the loop body. It will increment the value by adding 1 to the previous value of the variable:

Example 2: Reverse Iteration Using While Loop

The following is the code that will print the first ten numbers in the reverse order starting from 10:

do $$
 declare 
  counter integer := 10;
 begin
  while counter >= 1 loop
  raise notice 'Counter %', counter;
   counter := counter - 1;
  end loop;
 end$$;

The above code will simply change the starting point and the condition value to reverse the order of the printing values. It will subtract 1 from the existing value as it starts from 10 and it will keep executing by subtracting 1 in each iteration until it becomes less than 1:

Example 3: Customizing Steps Using While Loop

Use the following code to print the multiples of two starting from 1 using the while loop in PostgreSQL:

do $$
 declare 
  counter integer := 1;
 begin
  while counter <= 10 loop
  raise notice 'Counter %', counter;
   counter := counter *2;
  end loop;
 end$$;

The above code creates a variable for the while loop containing value 1 and it will multiply the value with two until it becomes greater than 10. It starts with 1, multiplies it with 2, and so on until the loop terminates:

Example 4: While Loop in PostgreSQL Table

Run the following code to get data from the orders table:

SELECT * FROM orders;

Use the following query to get the total revenue from each customer:

DO $$
   DECLARE
  cust_id integer;
  total_revenue numeric;
 BEGIN
  cust_id := 1;
  WHILE cust_id <= (SELECT MAX(customer_id) FROM orders) LOOP
  SELECT SUM(order_total) INTO total_revenue FROM orders WHERE customer_id = cust_id;
  RAISE NOTICE 'Customer %   total revenue: %', cust_id, total_revenue;
  cust_id := cust_id + 1;
  END LOOP;
 END $$;

The above query contains:

- It creates two variables named “cust_id” and “total_revenue” to be used in the while loop.
- Starting value for the “cust_id” is given so the loop can check the condition using it.
- The “cust_id” variable will contain the value of the current “customer_id” on which the loop is being applied.
- The “total_revenue” variable will be used to store the sum of the values from the order-total column of each customer.
- It will print the total revenue on the screen for each customer individually by adding it using iterations of the loop:

That’s all about using a While loop in PostgreSQL.

Conclusion

In PostgreSQL, a While loop is used to perform multiple iterations on the same query by checking the condition before each iteration. The while loop is also called a pre-tested loop because it checks the condition before executing the query for each iteration. This guide has explained the While loop in PostgreSQL using multiple examples.