This is the multi-page printable view of this section. Click here to print.
PL/vSQL is a powerful and expressive procedural language for creating reusable procedures, manipulating data, and simplifying otherwise complex database routines.
Vertica PL/vSQL is largely compatible with PostgreSQL PL/pgSQL, with minor semantic differences. For details on migrating your PostgreSQL PL/pgSQL stored procedures to Vertica, see the PL/pgSQL to PL/vSQL migration guide.
For real-world, practical examples of PL/vSQL usage, see Stored procedures: use cases and examples.
Vertica PL/vSQL supports non-complex data types. The following types are supported as variables only and not as arguments:
DECIMAL
NUMERIC
NUMBER
MONEY
UUID
PL/vSQL uses block scope, where a block has the following structure:
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
...
END [ label ];
Variable declarations in the DECLARE block are structured as:
variable_name [ CONSTANT ] data_type [ NOT NULL ] [:= { expression | statement } ];
variable_name |
Variable names must meet the following requirements:
|
CONSTANT |
Defines the variable as a constant (immutable). You can only set a constant variable's value during initialization. |
data_type |
The variable data type. PL/vSQL supports non-complex data types, with the following exceptions:
You can optionally reference a particular column's data type:
|
NOT NULL |
Specifies that the variable cannot hold a NULL value. If declared with NOT NULL, the variable must be initialized (otherwise, throws ERRCODE_SYNTAX_ERROR) and cannot be assigned NULL (otherwise, throws ERRCODE_WRONG_OBJECT_TYPE). |
:= expression |
Initializes a variable with If the variable is declared with Variable declarations in a given block execute sequentially, so old declarations can be referenced by newer ones. For example: Default (uninitialized): NULL |
Aliases are alternate names for the same variable. An alias of a variable is not a copy, and changes made to either reference affect the same underlying variable.
new_name ALIAS FOR variable;
Here, the identifier y is now an alias for variable x, and changes to y are reflected in x.
DO $$
DECLARE
x int := 3;
y ALIAS FOR x;
BEGIN
y := 5; -- since y refers to x, x = 5
RAISE INFO 'x = %, y = %', x, y;
END;
$$;
INFO 2005: x = 5, y = 5
BEGIN contains statements. A statement is defined as a line or block of PL/vSQL.
Variables declared in inner blocks shadow those declared in outer blocks. To unambiguously specify a variable in a particular block, you can name the block with a label (case-insensitive), and then reference the variable declared in that block with:
label.variable_name
For example, specifying the variable x from inside the inner block implicitly refers to inner_block.x rather than outer_block.x because of shadowing:
<<outer_block>>
DECLARE
x int;
BEGIN
<<inner_block>>
DECLARE
x int;
BEGIN
x := 1000; -- implicitly specifies x in inner_block because of shadowing
OUTER_BLOCK.x := 0; -- specifies x in outer_block; labels are case-insensitive
END inner_block;
END outer_block;
The NULL statement does nothing. This can be useful as a placeholder statement or a way to show that a code block is intentionally empty. For example:
DO $$
BEGIN
NULL;
END;
$$
Comments have the following syntax. You cannot nest comments.
-- single-line comment
/* multi-line
comment
*/
Stored procedures that call other stored procedures, also called nested stored procedures, can be useful for simplifying complex functions and reusing code.
You can enable nested stored procedures by setting the EnableNestedStoredProcedures configuration parameter (disabled by default):
--Enable nested calls
=> ALTER DATABASE DEFAULT SET EnableNestedStoredProcedures = 1;
--Disable nested calls
=> ALTER DATABASE DEFAULT SET EnableNestedStoredProcedures = 0;
In the following example, proc2() calls proc1() to insert values into a table:
CREATE PROCEDURE proc1() AS
$$
BEGIN PERFORM INSERT INTO t_int VALUES(2023);
END;
$$;
CREATE PROCEDURE proc2() AS $$
BEGIN
PERFORM CREATE TABLE IF NOT EXISTS t_int(x int);
PERFORM CALL proc1();
END;
$$;
You can also use this feature to call meta-functions:
CREATE PROCEDURE RUN_ANALYZE_STATS() AS
$$
BEGIN PERFORM SELECT analyze_statistics('');
END;
$$;
CALL run_analyze_stats();
Stored procedures can only be nested up to a depth of 50. If a stored procedure exceeds the call depth, the entire operation is terminated and rolled back.
The stored procedure recursive_proc() calls itself to insert sequential values into a table, but it has no condition to stop before the depth limit. Calling the procedure causes a rollback and no changes are made to the table:
=> CREATE TABLE numbers (n INT);
=> SELECT * FROM numbers;
n
---
(0 rows)
=> CREATE OR REPLACE PROCEDURE recursive_proc(x int) AS
$$
BEGIN
PERFORM INSERT INTO numbers VALUES(x + 1);
PERFORM CALL recursive_proc(x + 1);
END;
$$;
=> CALL recursive_proc(0);
ERROR 0: Nested stored procedure call exceeds call depth limit
CONTEXT: PL/vSQL procedure recursive_proc line 4 at static SQL
PL/vSQL procedure recursive_proc line 4 at static SQL
PL/vSQL procedure recursive_proc line 4 at static SQL
...
=> SELECT * FROM numbers;
n
---
(0 rows)
You can embed and execute SQL statements and expressions from within stored procedures.
To save the value of an expression or returned value, you can assign it to a variable:
variable_name := expression;
variable_name := statement;
For example, this procedure assigns 3 into i and 'message' into v.
=> CREATE PROCEDURE performless_assignment() LANGUAGE PLvSQL AS $$
DECLARE
i int;
v varchar;
BEGIN
i := SELECT 3;
v := 'message';
END;
$$;
This type of assignment will fail if the query returns no rows or more than one row. For returns of multiple rows, use LIMIT or truncating assignment:
=> SELECT * FROM t1;
b
---
t
f
f
(3 rows)
=> CREATE PROCEDURE more_than_one_row() LANGUAGE PLvSQL as $$
DECLARE
x boolean;
BEGIN
x := SELECT * FROM t1;
END;
$$;
CREATE PROCEDURE
=> CALL more_than_one_row();
ERROR 10332: Query returned multiple rows where 1 was expected
Truncating assignment stores in a variable the first row returned by a query. Row order is nondeterministic unless you specify an ORDER BY clause:
variable_name <- expression;
variable_name <- statement;
The following procedure takes the first row of the results returned by the specified query and assigns it to x:
=> CREATE PROCEDURE truncating_assignment() LANGUAGE PLvSQL AS $$
DECLARE
x boolean;
BEGIN
x <- SELECT * FROM t1 ORDER BY b DESC; -- x is now assigned the first row returned by the SELECT query
END;
$$;
The PERFORM keyword runs a SQL statement or expression and discards the returned result.
PERFORM statement;
PERFORM expression;
For example, this procedure inserts a value into a table. INSERT returns the number of rows inserted, so you must pair it with PERFORM.
=> DO $$
BEGIN
PERFORM INSERT INTO coordinates VALUES(1,2,3);
END;
$$;
EXECUTE allows you to dynamically construct a SQL query during execution:
EXECUTE command_expression [ USING expression [, ... ] ];
command_expression is a SQL expression that can reference PL/vSQL variables and evaluates to a string literal. The string literal is executed as a SQL statement, and $1, $2, ... are substituted with the corresponding *expression*s.
Constructing your query with PL/vSQL variables can be dangerous and expose your system to SQL injection, so wrap them with QUOTE_IDENT, QUOTE_LITERAL, and QUOTE_NULLABLE.
The following procedure constructs a query with a WHERE clause:
DO $$
BEGIN
EXECUTE 'SELECT * FROM t1 WHERE x = $1' USING 10; -- becomes WHERE x = 10
END;
$$;
The following procedure creates a user with a password from the username and password arguments. Because the constructed CREATE USER statement uses variables, use the functions QUOTE_IDENT and QUOTE_LITERAL, concatenating them with ||.
=> CREATE PROCEDURE create_user(username varchar, password varchar) LANGUAGE PLvSQL AS $$
BEGIN
EXECUTE 'CREATE USER ' || QUOTE_IDENT(username) || ' IDENTIFIED BY ' || QUOTE_LITERAL(password);
END;
$$;
EXECUTE is a SQL statement, so you can assign it to a variable or pair it with PERFORM:
variable_name:= EXECUTE command_expression;
PERFORM EXECUTE command_expression;
The special boolean variable FOUND is initialized as false and assigned true or false based on whether:
A statement (but not expression) returns results with non-zero number of rows, or
A FOR loop iterates at least once
You can use FOUND to distinguish between a NULL and 0-row return.
Special variables exist between the scope of a procedure's argument and the outermost block of its definition. This means that:
Special variables shadow procedure arguments
Variables declared in the body of the stored procedure will shadow the special variable
The following procedure demonstrates how FOUND changes. Before the SELECT statement, FOUND is false; after the SELECT statement, FOUND is true.
=> DO $$
BEGIN
RAISE NOTICE 'Before SELECT, FOUND = %', FOUND;
PERFORM SELECT 1; -- SELECT returns 1
RAISE NOTICE 'After SELECT, FOUND = %', FOUND;
END;
$$;
NOTICE 2005: Before SELECT, FOUND = f
NOTICE 2005: After SELECT, FOUND = t
Similarly, UPDATE, DELETE, and INSERT return the number of rows affected. In the next example, UPDATE doesn't change any rows, but returns the value 0 to indicate that no rows were affected, so FOUND is set to true:
=> SELECT * t1;
a | b
-----+-----
100 | abc
(1 row)
DO $$
BEGIN
PERFORM UPDATE t1 SET a=200 WHERE b='efg'; -- no rows affected since b doesn't contain 'efg'
RAISE INFO 'FOUND = %', FOUND;
END;
$$;
INFO 2005: FOUND = t
FOUND starts as false and is set to true if the loop iterates at least once:
=> DO $$
BEGIN
RAISE NOTICE 'FOUND = %', FOUND;
FOR i IN RANGE 1..1 LOOP -- RANGE is inclusive, so iterates once
RAISE NOTICE 'i = %', i;
END LOOP;
RAISE NOTICE 'FOUND = %', FOUND;
END;
$$;
NOTICE 2005: FOUND = f
NOTICE 2005: FOUND = t
DO $$
BEGIN
RAISE NOTICE 'FOUND = %', FOUND;
FOR i IN RANGE 1..0 LOOP
RAISE NOTICE 'i = %', i;
END LOOP;
RAISE NOTICE 'FOUND = %', FOUND;
END;
$$;
NOTICE 2005: FOUND = f
NOTICE 2005: FOUND = f
Control flow constructs give you control over how many times and under what conditions a block of statements should run.
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 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 repeatedly execute a block of code until a given condition is satisfied.
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
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 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.
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
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
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
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;
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 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
ASSERT is a debugging feature that checks whether a condition is true. If the condition is false, ASSERT raises an ASSERT_FAILURE exception with an optional error message.
To escape a ' (single quote) character, use ''. Similarly, to escape a " (double quote) character, use "".
ASSERT condition [ , message ];
For example, this procedure checks the number of rows in the products table and uses ASSERT to check that the table is populated. If the table is empty, Vertica raises an error:
=> CREATE TABLE products(id UUID, name VARCHARE, price MONEY);
CREATE TABLE
=> SELECT * FROM products;
id | name | price
----+------+-------
(0 rows)
DO $$
DECLARE
prod_count INT;
BEGIN
prod_count := SELECT count(*) FROM products;
ASSERT prod_count > 0, 'products table is empty';
END;
$$;
ERROR 2005: products table is empty
To stop Vertica from checking ASSERT statements, you can set the boolean session-level parameter PLpgSQLCheckAsserts.
RAISE can throw errors or print a user-specified error message, one of the following:
RAISE [ level ] 'format' [, arg_expression [, ... ]] [ USING option = expression [, ... ] ];
RAISE [ level ] condition_name [ USING option = expression [, ... ] ];
RAISE [ level ] SQLSTATE 'sql-state' [ USING option = expression [, ... ] ];
RAISE [ level ] USING option = expression [, ... ];
level |
VARCHAR, one of the following:
Default: EXCEPTION | ||||||||||||||||||||
format |
VARCHAR, a string literal error message where the percent character If the number of To escape a | ||||||||||||||||||||
arg_expression |
An expression that substitutes for the percent character (%) in the format string. | ||||||||||||||||||||
option = expression |
|
This procedure demonstrates various RAISE levels:
=> DO $$
DECLARE
logfile varchar := 'vertica.log';
BEGIN
RAISE LOG 'this message was sent to %', logfile;
RAISE INFO 'info';
RAISE NOTICE 'notice';
RAISE WARNING 'warning';
RAISE EXCEPTION 'exception';
RAISE NOTICE 'exception changes control flow; this is not printed';
END;
$$;
INFO 2005: info
NOTICE 2005: notice
WARNING 2005: warning
ERROR 2005: exception
$ grep 'this message was sent to vertica.log' v_vmart_node0001_catalog/vertica.log
<LOG> @v_vmart_node0001: V0002/2005: this message is sent to vertica.log
EXCEPTION blocks let you catch and handle exceptions that might get thrown from statements:
[ <<label>> ]
[ DECLARE
declarations ]
BEGIN
statements
EXCEPTION
WHEN exception_condition [ OR exception_condition ... ] THEN
handler_statements
[ WHEN exception_condition [ OR exception_condition ... ] THEN
handler_statements
... ]
END [ label ];
exception_condition has one of the following forms:
WHEN errcode_division_by_zero THEN ...
WHEN division_by_zero THEN ...
WHEN SQLSTATE '22012' THEN ...
WHEN OTHERS THEN ...
OTHERS is a special condition that catches all exceptions except QUERY_CANCELLED, ASSERT_FAILURE, and FEATURE_NOT_SUPPORTED.
When an exception is thrown, Vertica checks the list of exceptions for a matching exception_condition from top to bottom. If it finds a match, it executes the handler_statements and then leaves the exception block's scope.
If Vertica can't find a match, it propagates the exception up to the next enclosing block. You can do this manually within an exception handler with RAISE:
RAISE;
For example, the following procedure divides 3 by 0 in the inner_block, which is an illegal operation that throws the exception division_by_zero with SQL state 22012. Vertica checks the inner EXCEPTION block for a matching condition:
The first condition checks for SQL state 42501, so Vertica moves to the next condition.
WHEN OTHERS THEN catches all exceptions, it executes that block.
The bare RAISE then propagates the exception to the outer_block.
The outer EXCEPTION block successfully catches the exception and prints a message.
=> DO $$
<<outer_block>>
BEGIN
<<inner_block>>
DECLARE
x int;
BEGIN
x := 3 / 0; -- throws exception division_by_zero, SQLSTATE 22012
EXCEPTION -- this block is checked first for matching exceptions
WHEN SQLSTATE '42501' THEN
RAISE NOTICE 'caught insufficient_privilege exception';
WHEN OTHERS THEN -- catches all exceptions
RAISE; -- manually propagate the exception to the next enclosing block
END inner_block;
EXCEPTION -- exception is propagated to this block
WHEN division_by_zero THEN
RAISE NOTICE 'caught division_by_zero exception';
END outer_block;
$$;
NOTICE 2005: caught division_by_zero exception
When handling an exception, you can use the following variables to retrieve error information:
SQLSTATE contains the SQL state
SQLERRM contains the error message
For details, see SQL state list.
This procedure catches the exception thrown by attempting to assign NULL to a NOT NULL variable and prints the SQL state and error message:
DO $$
DECLARE
i int NOT NULL := 1;
BEGIN
i := NULL; -- illegal, i was declared with NOT NULL
EXCEPTION
WHEN OTHERS THEN
RAISE WARNING 'SQL State: %', SQLSTATE;
RAISE WARNING 'Error message: %', SQLERRM;
END;
$$;
WARNING 2005: SQLSTATE: 42809
WARNING 2005: SQLERRM: Cannot assign null into NOT NULL variable
You can retrieve information about exceptions inside exception handlers with GET STACKED DIAGNOSTICS:
GET STACKED DIAGNOSTICS variable_name { = | := } item [, ... ];
Where item can be any of the following:
| item | Description |
|---|---|
RETURNED_SQLSTATE |
SQLSTATE error code of the exception |
COLUMN_NAME |
Name of the column related to exception |
CONSTRAINT_NAME |
Name of the constraint related to exception |
DATATYPE_NAME |
Name of the data type related to exception |
MESSAGE_TEXT |
Text of the exception's primary message |
TABLE_NAME |
Name of the table related to exception |
SCHEMA_NAME |
Name of the schema related to exception |
DETAIL_TEXT |
Text of the exception's detail message, if any |
HINT_TEXT |
Text of the exception's hint message, if any |
EXCEPTION_CONTEXT |
Description of the call stack at the time of the exception |
For example, this procedure has an EXCEPTION block that catches the division_by_zero error and prints SQL state, error message, and the exception context:
=> DO $$
DECLARE
message_1 varchar;
message_2 varchar;
message_3 varchar;
x int;
BEGIN
x := 5 / 0;
EXCEPTION
WHEN OTHERS THEN -- OTHERS catches all exceptions
GET STACKED DIAGNOSTICS message_1 = RETURNED_SQLSTATE,
message_2 = MESSAGE_TEXT,
message_3 = EXCEPTION_CONTEXT;
RAISE INFO 'SQLSTATE: %', message_1;
RAISE INFO 'MESSAGE: %', message_2;
RAISE INFO 'EXCEPTION_CONTEXT: %', message_3;
END;
$$;
INFO 2005: SQLSTATE: 22012
INFO 2005: MESSAGE: Division by zero
INFO 2005: EXCEPTION_CONTEXT: PL/vSQL procedure inline_code_block line 8 at static SQL
A cursor is a reference to the result set of a query and allows you to view the results one row at a time. Cursors remember their positions in result sets, which can be one of the following:
a result row
before the first row
after the last row
You can also iterate over unopened, bound cursors with a FOR loop. See Control Flow for more information.
To bind a cursor to a statement on declaration, use the FOR keyword:
cursor_name CURSOR [ ( arg_name arg_type [, ...] ) ] FOR statement;
The arguments to a cursor give you more control over which rows to process. For example, suppose you have the following table:
=> SELECT * FROM coordinates_xy;
x | y
---+----
1 | 2
9 | 5
7 | 13
...
(100000 rows)
If you're only interested in the rows where y is 6, you might declare the following cursor and then provide the argument 6 when you OPEN the cursor:
c CURSOR (key int) FOR SELECT * FROM coordinates_xy WHERE y=key;
To declare a cursor without binding it to a particular query, use the refcursor type:
cursor_name refcursor;
You can bind an unbound cursor at any time with OPEN.
For example, to declare the cursor my_unbound_cursor:
my_unbound_cursor refcursor;
Opening a cursor executes the query with the given arguments, and puts the cursor before the first row of the result set. The ordering of query results (and therefore, the start of the result set) is non-deterministic, unless you specify an ORDER BY clause.
To open a cursor that was bound during declaration:
OPEN bound_cursor [ ( [ arg_name := ] arg_value [, ...] ) ];
For example, given the following declaration:
c CURSOR (key int) FOR SELECT * FROM t1 WHERE y=key;
You can open the cursor with one of the following:
OPEN c(5);
OPEN c(key := 5);
Open cursors are automatically closed when the cursor leaves scope, but you can close the cursor preemptively with CLOSE. Closed cursors can be reopened later, which re-executes the query and prepares a new result set.
CLOSE cursor;
To bind an unbound cursor and then open it:
OPEN unbound_cursor FOR statement;
You can also use EXECUTE because it's a statement:
OPEN unbound_cursor FOR EXECUTE statement_string [ USING expression [, ... ] ];
For example, to bind the cursor c to a query to a table product_data:
OPEN c for SELECT * FROM product_data;
FETCH statements:
Retrieve the row that the specified cursor currently points to and stores it in some variable.
Advance the cursor to the next position.
variable [, ...] := FETCH opened_cursor;
The retrieved value is stored in variable. Rows typically have more than one value, so you can use one variable for each.
If FETCH successfully retrieves a value, the special variable FOUND is set to true. Otherwise, if you call FETCH when the cursor is past the final row of the result set, it returns NULL and the special variable FOUND is set to false.
The following procedure creates a cursor c, binding it to a SELECT query on the coordinates table. The procedures passes the argument 1 to the cursor, so the cursor only retrieves rows where the y-coordinate is 1, storing the coordinates in the variables x_, y_, and z_.
Only two rows have a y-coordinate of 1, so after using FETCH twice, the third FETCH starts to return NULL values and FOUND is set to false:
=> 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
OPEN c(1); -- only retrieve rows where y=1
x_,y_,z_ := FETCH c;
RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
x_,y_,z_ := FETCH c; -- fetches the last set of results and moves to the end of the result set
RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
x_,y_,z_ := FETCH c; -- cursor has advanced past the final row
RAISE NOTICE 'cursor returned %, %, %, FOUND=%',x_, y_, z_, FOUND;
END;
$$;
NOTICE 2005: cursor returned 7, 1, 10, FOUND=t
NOTICE 2005: cursor returned 67, 1, 77, FOUND=t
NOTICE 2005: cursor returned <NULL>, <NULL>, <NULL>, FOUND=f
MOVE advances an open cursor to the next position without retrieving the row. The special FOUND variable is set to true if the cursor's position (before MOVE) was not past the final row—that is, if calling FETCH instead of MOVE would have retrieved the row.
MOVE bound_cursor;
For example, this cursor only retrieves rows where the y-coordinate is 2. The result set is only one row, so using MOVE twice advances past the first (and last) row, setting FOUND to false:
=> SELECT * FROM coordinates WHERE y=2;
x | y | z
----+---+---
10 | 2 | 1
(1 row)
DO $$
DECLARE
c CURSOR (key int) FOR SELECT * FROM coordinates WHERE y=key;
BEGIN
OPEN c(2); -- only retrieve rows where y=2, cursor starts before the first row
MOVE c; -- cursor advances to the first (and last) row
RAISE NOTICE 'FOUND=%', FOUND; -- FOUND is true because the cursor points to a row in the result set
MOVE c; -- cursor advances past the final row
RAISE NOTICE 'FOUND=%', FOUND; -- FOUND is false because the cursor is past the final row
END;
$$;
NOTICE 2005: FOUND=t
NOTICE 2005: FOUND=f
While Vertica PL/vSQL is largely compatible with PostgreSQL PL/pgSQL, there are some easily-resolved semantic and SQL-level differences when migrating from PostgreSQL PL/pgSQL.
PL/vSQL differs from PL/pgSQL in the following ways:
You must use the PERFORM statement for SQL statements that return no value.
UPDATE/DELETE WHERE CURRENT OF is not currently supported.
FOR loops have additional keywords:
FOR (RANGE) loops: RANGE keyword
FOR (QUERY) loops: QUERY keyword
FOR (CURSOR) loops: CURSOR keyword
By default, NULL cannot be coerced to FALSE.
NULL is not coercible to FALSE by default, and expressions that expect a BOOLEAN value throw an exception when given a NULL:
=> DO $$
BEGIN
IF NULL THEN -- BOOLEAN value expected for IF
END IF;
END;
$$;
ERROR 10268: Query returned null where a value was expected
To enable NULL-to-FALSE coercion, set the configuration parameter PLvSQLCoerceNull:
=> ALTER DATABASE DEFAULT SET PLvSQLCoerceNull = 1;
Support for the following features is planned for a future release:
=> CREATE TABLE t1(i int);
CREATE TABLE
=> DO $$
DECLARE
x int;
BEGIN
x := INSERT INTO t1 VALUES (200);
RAISE INFO 'rows inserted: %', x;
END;
$$;
INFO 2005: rows inserted: 1
Vertica differs from PostgreSQL in the following ways:
Some data types are different sizes—for example, the standard INTEGER type in Vertica is 8 bytes, but 4 bytes in PostgreSQL.
In Vertica, INSERT, UPDATE, and DELETE return the number of rows affected.
Certain SQLSTATE codes are different, which affects exception handling.