Loops in PostgreSQL databases are used to repeat the same query multiple times to fetch data from the PostgreSQL table. Loops should contain the starting and ending range to limit the iterations so they can fetch data from tables to stop it from going indefinitely. But in some conditions, the user wants to stop the loop before the ending condition which can be done with the help of the “Exit” statement.

This post demonstrates the process to use the exit statement to terminate a loop in PostgreSQL.

PL/pgSQL Exit Statement: How to Terminate a Loop

The EXIT statement can be used to terminate the body of the loop before the actual ending of the loop by providing some conditions to this statement. The exit statement generally uses boolean expressions as the condition statement but it is optional as the use of loop label with it. Additionally, it provides control to the looping structures and conditional statements by providing them with the exact point of exit.

Syntax

This is the syntax for the exit statement used in PostgreSQL:

exit [label] [when boolean_expression];

The above code contains the exit keyword and label of the loop with the condition on which the loop will be terminated.

The following code block contains the exit keyword with the when clause containing the variable name and the condition. It suggests that the loop will terminate as soon as the value of the counter variable reaches 10:

exit when counter > 10;

The following code can be used to terminate the block which is contained inside the begin..end keyword:

if counter > 10 then
  exit;
   end if;

Example 1: Using Exit Statement to Terminate a Loop

Use the following code which terminates the loop before the end of its actual conditions:

DO $$
 DECLARE
  a INTEGER := 1;
 BEGIN
  WHILE a <= 10 LOOP
  RAISE NOTICE 'Variable value: %', a;
  IF a = 5 THEN
  EXIT;
  END IF;
  a := a + 1;
  END LOOP;
 END $$;

Here:

- Code starts with the loop variable named “a” having value 1 stored in it and begins the loop with a condition to be set at 10.
- It starts executing the iterations but the exit statement suggests that the loop should be terminated at 5.
- The loop was supposed to end when the variable value exceeds 10 but the exit statement forced to terminate the loop when the value reached 5 and printed on the screen:

Example 2: Using Exit Statement to Terminate a Nested Loop

The following query will terminate a loop while there are nested loops in progress and print values on the screen:

DO $$
 DECLARE
  outer_counter INTEGER;
  inner_counter INTEGER;
 BEGIN
  FOR outer_counter IN 1..3 LOOP
  RAISE NOTICE 'Outer Counter: %', outer_counter;
  
  FOR inner_counter IN 1..4 LOOP
  RAISE NOTICE 'Inner Counter: %', inner_counter;
  
  IF inner_counter = 3 THEN
  EXIT;
  END IF;
  END LOOP;
  
  IF outer_counter = 2 THEN
  EXIT;
  END IF;
  END LOOP;
 END $$;

Here in the above code:

- Two variables have been declared named outer_counter and inner_counter for outer and inner loops respectively.
- The outer loop is supposed to run from 1 to 3 and the inner loop is to execute inside the outer loop from 1 to 4.
- The exit statement suggests that the outer loop runs twice and inside it, the inner loop will run from 1 to 3.
- So, each time the outer loop runs the inner loop will execute three times and the output will be printed on the screen:

Example 3: Using Exit Statement to Terminate a Loop in PostgreSQL Table

Use the following query to get data from the orders table and then use the exit statement in the table:

SELECT * FROM orders;

Running the above code will display all the data from the orders table:

The following code will be used to apply the EXIT statement on the PostgreSQL table:

DO $$
 DECLARE
  cust_id integer;
  total_revenue numeric;
 BEGIN
  FOR cust_id IN SELECT DISTINCT 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;
  
   -- Check for the exit condition
  IF cust_id = 2 THEN
  EXIT;
  END IF;
  END LOOP;
 END $$;

Let’s comprehend the above code stepwise:

- It creates two variables named cust_id and total_revenue for storing values of customer_id and order_total respectively.
- It will calculate the order_total from each customer and store its sum in the total_revenue variable.
- The loop is supposed to get each customer's revenue but the exit statement stops it when the cust_id variable has the value 2.
- It will print the data for only two customers and will leave the revenue for the third customer:

That’s all about using the exit statement to terminate a loop.

Conclusion

In PostgreSQL, the loops are used to run a single query multiple times with the given range that refers to their starting and ending point. The loop is given an ending point called condition and only runs the statements until it reaches that point. However, sometimes the user needs to stop the loop before the endpoint, here comes the exit statement to terminate the loop. This guide has explained the use of exit statements in PostgreSQL with multiple examples.