Control flow

Control flow constructs give you control over how many times and under what conditions a block of statements should run.

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