PostgreSQL – Exit Statement
In PostgreSQL, the EXIT statement is a powerful tool used to terminate loops and blocks of code. This functionality is essential for managing control flow within your PostgreSQL scripts, allowing for more efficient and readable code. Let us get a better understanding of the usage of the EXIT statement, its syntax, and practical examples to help you master its application.
Using EXIT for loops
The EXIT statement in PostgreSQL can terminate different types of loops, including unconditional loops, while loops, and for loops.
Syntax
EXIT [label] [WHEN condition];
Parameters:
- Label: The label is used to signify the loop which we want to exit. It is often used in the case of nested looping. If a label is not present, the current loop is terminated.
- Condition: The condition is a simple boolean expression that determines when we want to terminate the loop. When the value of the boolean expression becomes true, the loop is terminated.
Both the label and condition are optional. We can use exit with a condition like:
EXIT WHEN cnt < 5;
Alternatively, without a condition, you can use an IF statement to achieve the same result:
IF cnt < 5 THEN
EXIT;
END IF;
Example: Terminating a Loop
Suppose we a have loop that is used to print all numbers from 1 to 10. We can use the EXIT statement in the following manner to limit printing the numbers up to 7 only.
DO $$
DECLARE
n INTEGER := 8;
cnt INTEGER := 1;
BEGIN
LOOP
EXIT WHEN cnt = n;
RAISE NOTICE '%', cnt;
cnt := cnt + 1;
END LOOP;
END $$;
Output:
Explanation: In the above example, we terminate our loop as soon as the value of our cnt variable reaches n(here 8) and thus, only values up to 7 are printed.
Using EXIT to Exit a block
The EXIT statement can also be used to exit a block of code specified by the BEGIN..END keywords. This is particularly useful for prematurely terminating a block to skip the execution of subsequent statements.
Syntax
<<block_label>>
BEGIN
Statements
EXIT [block_label] [WHEN condition];
Statements
END block_label;
Using this syntax, we can terminate the block of code prematurely, thus preventing the statements after the exit to be run.
Example: Exiting a Block
The following example shows how we can use EXIT to exit a block.
DO
$$
BEGIN
RAISE NOTICE '%', 'Before block';
<<normalblock>>
BEGIN
RAISE NOTICE '%', 'Before exit; inside block';
EXIT normalblock;
RAISE NOTICE '%', 'After exit; inside block';
END;
RAISE NOTICE '%', 'End of block';
END;
$$;
Output:
Explanation: In the above example, the statement after exit was not printed as the block was terminated using EXIT before the statement. Thus inside the block, only statements before EXIT were executed and after that, the flow simply passes after the block ended.
Important Points About EXIT Statement in PostgreSQL
- The EXIT statement in PostgreSQL is used to terminate loops and blocks of code in PostgreSQL.
- It can be applied to all types of loops, including unconditional loops, while loops, and for loops, as well as to code blocks defined by
BEGIN..END.- Without a condition, the EXIT statement can be replaced with an IF statement to achieve the same result.
- Labels are useful for exiting specific loops or blocks, especially in nested structures. If a label is not specified, the current loop or block is exited.
