Summary: in this tutorial, you will learn about the exit statement and how to use it to terminate a loop or a block.
Introduction to the PL/pgSQL Exit statement
The exit
statement allows you to terminate a loop including an unconditional loop, a while loop, and a for loop.
The following shows the syntax of the exit
statement:
exit [label] [when boolean_expression]Code language: CSS (css)
The label
is the loop label of the current loop where the exit
is in or the loop label of the outer loop. Depending on the label, the exit
statement will terminate the corresponding loop. If you don’t use the label, the exit
statement will terminate the current loop.
The when boolean_expression
clause is used to specify a condition that terminates a loop. The exit
statement will terminate the loop if the boolean_expression
evaluates to true
.
The following statements are equivalent:
exit when counter > 10;
if counter > 10 then
exit;
end if;
The exit when
is definitely cleaner and shorter.
In addition to terminating a loop, you can use the exit
statement to terminate a block specified by the begin...end
keywords. In this case, the control is passed to the statement after the end
keyword of the current block:
<>
BEGIN
-- some code
EXIT [block_label] [WHEN condition];
-- some more code
END block_label;
PL/pgSQL Exit examples
Let’s take some examples of using the PL/pgSQL exit
statement.
1) Using PL/pgSQL Exit statement to terminate an unconditional loop
The following example illustrates how to use the exit
statement in unconditional loops:
do $$ declare i int = 0; j int = 0; begin <> loop i = i + 1; exit when i > 3; -- inner loop j = 0; <> loop j = j + 1; exit when j > 3; raise notice '(i,j): (%,%)', i, j; end loop inner_loop; end loop outer_loop; end; $$
Output:
NOTICE: (i,j): (1,1)
NOTICE: (i,j): (1,2)
NOTICE: (i,j): (1,3)
NOTICE: (i,j): (2,1)
NOTICE: (i,j): (2,2)
NOTICE: (i,j): (2,3)
NOTICE: (i,j): (3,1)
NOTICE: (i,j): (3,2)
NOTICE: (i,j): (3,3)
How it works.
This example contains two loops: outer and inner loops.
Since both exit
statements don’t use any loop labels, they will terminate the current loop.
The first exit
statement terminates the outer loop when i
is greater than 3
. That’s why you see the value of i
in the output is 1
, 2
, and 3
.
The second exit
statement terminates the inner loop when j
is greater than 3
. It is the reason you see that j
is 1
, 2
, and 3
for each iteration of the outer loop.
The following example places the label of the outer loop in the second exit
statement:
do $$ declare i int = 0; j int = 0; begin <> loop i = i + 1; exit when i > 3; -- inner loop j = 0; <> loop j = j + 1; exit outer_loop when j > 3; raise notice '(i,j): (%,%)', i, j; end loop inner_loop; end loop outer_loop; end; $$
Output:
NOTICE: (i,j): (1,1)
NOTICE: (i,j): (1,2)
NOTICE: (i,j): (1,3)
In this example, the second exit
statement terminates the outer loop when j
is greater than 3.
2) Using the PL/pgSQL Exit statement to exit a block
The following example illustrates how to use the exit
statement to terminate a block:
do $$ begin <> begin exit simple_block; -- for demo purposes raise notice '%', 'unreachable!'; end; raise notice '%', 'End of block'; end; $$
Output
NOTICE: End of block
In this example, the exit statement terminates the simple_block
immediately:
exit simple_block;
This statement will never be reached:
raise notice '%', 'unreachable!';
Summary
- Use
exit
statement to terminate a loop including an unconditional loop,while
loop, andfor
loop. - Also use the
exit
statement to exit a block.
Leave a Reply