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.

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

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.

Coercing NULL 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;

Planned features

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

  • Full transaction semantics: Currently, stored procedures only COMMIT changes after successful execution. This means that you cannot manually ROLLBACK. However, changes made by nested stored procedures are automatically rolled back if they reach the depth limit. You can also use PERFORM COMMIT to commit changes during execution.
  • 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: To work around this, you can rely on INSERT, UPDATE, and DELETE to return the number of rows affected:
=> 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

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.