Control Flow
Control flow constructs give you control over how many times and under what conditions a block of statements should run.
Conditionals
IF/ELSIF/ELSE
IF/ELSIF/ELSE statements let you perform different actions based on a specified condition.
IF condition_1 THEN statement_1; [ ELSIF condition_2 THEN statement_2 ] ... [ ELSE statement_n; ] END IF;
Vertica successively evaluates each condition as a boolean until it finds one that's true, then executes the block of statements and exits the IF statement. If no conditions are true, it executes the ELSE block, if one exists.
IF i = 3 THEN... ELSIF 0 THEN... ELSIF true THEN... ELSIF x <= 4 OR x >= 10 THEN... ELSIF y = 'this' AND z = 'THAT' THEN...
For example, this procedure demonstrates a simple IF...ELSE branch. Because b is declared to be true, Vertica executes the first branch.
=> DO LANGUAGE PLvSQL $$
DECLARE
b bool := true;
BEGIN
IF b THEN
RAISE NOTICE 'true branch';
ELSE
RAISE NOTICE 'false branch';
END IF;
END;
$$;
NOTICE 2005: true branch
CASE
CASE expressions are often more readable than IF...ELSE chains. After executing a CASE expression's branch, control jumps to the statement after the enclosing END CASE.
PL/vSQL CASE expressions are more flexible and powerful than SQL case expressions, but the latter are more efficient; you should favor SQL case expressions when possible.
CASE [ search_expression ] WHEN expression_1 [, expression_2, ...] THEN when_statements [ ... ] [ ELSE else_statements ] END CASE;
search_expression is evaluated once and then compared with expression_n in each branch from top to bottom. If search_expression and a given expression_n are equal, then Vertica executes the WHEN block for expression_n and exits the CASE block. If no matching expression is found, the ELSE branch is executed, if one exists.
Case expressions must have either a matching case or an ELSE branch, otherwise Vertica throws a CASE_NOT_FOUND error.
If you omit search_expression, its value defaults to true.
For example, this procedure plays the game FizzBuzz, printing Fizz if the argument is divisible by 3, Buzz if the argument is divisible by 5, FizzBuzz if the if the argument is divisible by 3 and 5.
=> CREATE PROCEDURE fizzbuzz(IN x int) LANGUAGE PLvSQL AS $$
DECLARE
fizz int := x % 3;
buzz int := x % 5;
BEGIN
CASE fizz
WHEN 0 THEN -- if fizz = 0, execute WHEN block
CASE buzz
WHEN 0 THEN -- if buzz = 0, execute WHEN block
RAISE INFO 'FizzBuzz';
ELSE -- if buzz != 0, execute WHEN block
RAISE INFO 'Fizz';
END CASE;
ELSE -- if fizz != 0, execute ELSE block
CASE buzz
WHEN 0 THEN
RAISE INFO 'Buzz';
ELSE
RAISE INFO '';
END CASE;
END CASE;
END;
$$;
=> CALL fizzbuzz(3);
INFO 2005: Fizz
=> CALL fizzbuzz(5);
INFO 2005: Buzz
=> CALL fizzbuzz(15);
INFO 2005: FizzBuzz
Loops
Loops repeatedly execute a block of code until a given condition is satisfied.
WHILE
A WHILE loop checks a given condition and, if the condition is true, it executes the loop body, after which the condition is checked again: if true, the loop body executes again; if false, control jumps to the end of the loop body.
[ <<label>> ] WHILE condition LOOP statements; END LOOP;
For example, this procedure computes the factorial of the argument:
=> CREATE PROCEDURE factorialSP(input int) LANGUAGE PLvSQL AS $$
DECLARE
i int := 1;
output int := 1;
BEGIN
WHILE i <= input loop
output := output * i;
i := i + 1;
END LOOP;
RAISE INFO '%! = %', input, output;
END;
$$;
=> CALL factorialSP(5);
INFO 2005: 5! = 120
LOOP
This type of loop is equivalent to WHILE true and only terminates if it encounters a RETURN or EXIT
statement, or if an exception is thrown.
[ <<label>> ]
LOOP
statements;
END LOOP;
For example, this procedure prints the integers from counter up to upper_bound, inclusive:
DO $$
DECLARE
counter int := 1;
upper_bound int := 3;
BEGIN
LOOP
RAISE INFO '%', counter;
IF counter >= upper_bound THEN
RETURN;
END IF;
counter := counter + 1;
END LOOP;
END;
$$;
INFO 2005: 1
INFO 2005: 2
INFO 2005: 3
FOR
FOR loops iterate over a collection, which can be an integral range, query, or cursor.
If a FOR loop iterates at least once, the special FOUND variable is set to true after the loop ends. Otherwise, FOUND is set to false.
The FOUND variable can be useful for distinguishing between a NULL and 0-row return, or creating an IF branch if a LOOP didn't run.
FOR (RANGE)
A FOR (RANGE) loop iterates over a range of integers specified by the expressions left and right.
[ <<label>> ] FOR loop_counter IN RANGE [ REVERSE ] left..right [ BY step ] LOOP statements END LOOP [ label ];
loop_counter:
- does not have to be declared and is initialized with the value of left
- is only available within the scope of the FOR loop
loop_counter iterates from left to right (inclusive), incrementing by step at the end of each iteration.
The REVERSE option instead iterates from right to left (inclusive), decrementing by step.
For example, here is a standard ascending FOR loop with step = 1:
=> DO $$
BEGIN
FOR i IN RANGE 1..4 LOOP -- loop_counter i does not have to be declared
RAISE NOTICE 'i = %', i;
END LOOP;
RAISE NOTICE 'after loop: i = %', i; -- fails
END;
$$;
NOTICE 2005: i = 1
NOTICE 2005: i = 2
NOTICE 2005: i = 3
NOTICE 2005: i = 4
ERROR 2624: Column "i" does not exist -- loop_counter i is only available inside the FOR loop
Here, the loop_counter i starts at 4 and decrements by 2 at the end of each iteration:
=> DO $$
BEGIN
FOR i IN RANGE REVERSE 4..0 BY 2 LOOP
RAISE NOTICE 'i = %', i;
END LOOP;
END;
$$;
NOTICE 2005: i = 4
NOTICE 2005: i = 2
NOTICE 2005: i = 0
FOR (Query)
A FOR (QUERY) loop iterates over the results of a query.
[ <<label>> ] FOR target IN QUERY statement LOOP statements END LOOP [ label ];
You can include an ORDER BY clause in the query to make the ordering deterministic.
Unlike FOR (RANGE) loops, you must declare the target variables. The values of these variables persist after the loop ends.
For example, suppose given the table tuple:
=> SELECT * FROM tuples ORDER BY x ASC; x | y | z ---+---+--- 1 | 2 | 3 4 | 5 | 6 7 | 8 | 9 (3 rows)
This procedure retrieves the tuples in each row and stores them in the variables a, b, and c, and prints them after each iteration:
=>
=> DO $$
DECLARE
a int; -- target variables must be declared
b int;
c int;
i int := 1;
BEGIN
FOR a,b,c IN QUERY SELECT * FROM tuples ORDER BY x ASC LOOP
RAISE NOTICE 'iteration %: a = %, b = %, c = %', i,a,b,c;
i := i + 1;
END LOOP;
RAISE NOTICE 'after loop: a = %, b = %, c = %', a,b,c;
END;
$$;
NOTICE 2005: iteration 1: a = 1, b = 2, c = 3
NOTICE 2005: iteration 2: a = 4, b = 5, c = 6
NOTICE 2005: iteration 3: a = 7, b = 8, c = 9
NOTICE 2005: after loop: a = 7, b = 8, c = 9
You can also use a query constructed dynamically with EXECUTE:
[ <<label>> ] FOR target IN EXECUTE 'statement' [ USING expression [, ... ] ] LOOP statements END LOOP [ label ];
The following procedure uses EXECUTE to construct a FOR (QUERY) loop and stores the results of that SELECT statement in the variables x and y. The result set of a statement like this has only one row, so it only iterates once.
=> SELECT 'first string', 'second string';
?column? | ?column?
--------------+---------------
first string | second string
(1 row)
=> DO $$
DECLARE
x varchar; -- target variables must be declared
y varchar;
BEGIN
-- substitute the placeholders $1 and $2 with the strings
FOR x, y IN EXECUTE 'SELECT $1, $2' USING 'first string', 'second string' LOOP
RAISE NOTICE '%', x;
RAISE NOTICE '%', y;
END LOOP;
END;
$$;
NOTICE 2005: first string
NOTICE 2005: second string
FOR (Cursor)
A FOR (CURSOR) loop iterates over a bound, unopened cursor, executing some set of statements for each iteration.
[ <<label>> ] FOR loop_variable [, ...] IN CURSOR bound_unopened_cursor [ ( [ arg_name := ] arg_value [, ...] ) ] LOOP statements END LOOP [ label ];
This type of FOR loop opens the cursor at start of the loop and closes at the end.
For example, this procedure creates a cursor c. The procedure passes 6 as an argument to the cursor, so the cursor only retrieves rows where the y-coordinate is 6, storing the coordinates in the variables x_, y_, and z_ and printing them at the end of each iteration:
=> SELECT * FROM coordinates;
x | y | z
----+---+----
14 | 6 | 19
1 | 6 | 2
10 | 6 | 39
10 | 2 | 1
7 | 1 | 10
67 | 1 | 77
(6 rows)
DO $$
DECLARE
c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
x_ int;
y_ int;
z_ int;
BEGIN
FOR x_,y_,z_ IN CURSOR c(6) LOOP
RAISE NOTICE 'cursor returned %,%,% FOUND=%', x_,y_,z_,FOUND;
END LOOP;
RAISE NOTICE 'after loop: %,%,% FOUND=%', x_,y_,z_,FOUND;
END;
$$;
NOTICE 2005: cursor returned 14,6,19 FOUND=f -- FOUND is only set after the loop ends
NOTICE 2005: cursor returned 1,6,2 FOUND=f
NOTICE 2005: after loop: 10,6,39 FOUND=t -- x_, y_, and z_ retain their values, FOUND is now true because the FOR loop iterated at least once
Manipulating Loops
RETURN
You can exit the entire procedure (and therefore the loop) with RETURN. RETURN is an optional statement and can be added to signal to readers the end of a procedure.
RETURN;
EXIT
Similar to a break or labeled break in other programming languages, EXIT statements let you exit a loop early, optionally specifying:
- loop_label: the name of the loop to exit from
- condition: if the condition is
true, execute the EXIT statement
EXIT [ loop_label ] [ WHEN condition ];
CONTINUE
CONTINUE skips to the next iteration of the loop without executing statements that follow the CONTINUE itself. You can specify a particular loop with loop_label:
CONTINUE [loop_label] [ WHEN condition ];
For example, this procedure doesn't print during its first two iterations because the CONTINUE statement executes and moves on to the next iteration of the loop before control reaches the RAISE NOTICE statement:
=> DO $$
BEGIN
FOR i IN RANGE 1..5 LOOP
IF i < 3 THEN
CONTINUE;
END IF;
RAISE NOTICE 'i = %', i;
END LOOP;
END;
$$;
NOTICE 2005: i = 3
NOTICE 2005: i = 4
NOTICE 2005: i = 5