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.

Workaround: coercing NULL to FALSE

Unlike PostgreSQL PL/pgSQL, in Vertica PL/vSQL 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, enable 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 and session semantics. Currently, stored procedures commit the transaction before executing, and each embedded SQL statement executes in its own autocommitted 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.

  Note

  As a workaround, 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

The following list notes significant 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 to return the number of rows affected.

• Certain SQLSTATE codes are different, which affects exception handling.