PL/pgSQL to PL/vSQL Migration Guide

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.

Language-level Differences

The following is a list of notable differences between Vertica PL/vSQL and PostgreSQL PL/pgSQL. In Vertica PL/vSQL:

  • You must use the PERFORM statement for SQL statements that return no value.
  • UPDATE/DELETE WHERE CURRENT OF is not 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.

    • Vertica PL/vSQL differs from PostgreSQL PL/pgSQL in that, by default, NULLs are not coercible to false, 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 boolean PLvSQLCoerceNull parameter to true:

    => ALTER DATABASE DEFAULT SET PLvSQLCoerceNull = 1;

Planned Features

Support for the following features is planned for a future release:

  • Full transaction and session semantics. Currently, stored procedures commit the transaction before executing, and each embedded SQL statement executes in its own auto-committed transaction. This has the following implications:
    • You cannot ROLLBACK.
    • Session-level changes like creating directed queries or setting session-level parameters will succeed, but have no effect.
  • OUT/INOUT parameter modes.
  • FOREACH (ARRAY) loops.
  • Using the following types as arguments:
    • DECIMAL
    • NUMERIC
    • NUMBER
    • MONEY
    • UUID
  • Non-forward moving cursors.
  • CONTEXT/EXCEPTION_CONTEXT for diagnostics.
  • The special variable ROW_COUNT.

    • UPDATE, INSERT, and DELETE return the number of affected rows:

    => 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

SQL-level Differences

The following is a list of notable architectural, SQL-level differences between Vertica and PostgreSQL. In Vertica:

  • Some data types are different sizes—for example, the standard INTEGER type in Vertica is 8 bytes, but 4 bytes in PostgreSQL.
  • INSERT, UPDATE, and DELETE return the number of rows affected.
  • Certain SQLSTATE codes are different, which affects exception handling.