# PL/pgSQL Exit Statement: How to Terminate a Loop

> Terminating the loop before its actual ending point or condition can be done using the “Exit” statement in the PostgreSQL query.

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.

---
[View this page online](https://www.commandprompt.com/education/plpgsql-exit-statement-how-to-terminate-a-loop/)