SQL Functions and Statements

Command-Line Function Help

VSQL now supports command-line syntax help on supported functions, through queries on system table vs_procedure_columns. For example:

=> SELECT proc_oid, proc_name, column_name, ordinal_position FROM vs_procedure_columns WHERE proc_name = 'export_catalog';
 proc_oid |   proc_name    | column_name | ordinal_position
----------+----------------+-------------+------------------
     7974 | export_catalog | destination |                1
     7974 | export_catalog | scope       |                2
     7974 | export_catalog |             |                0
     8053 | export_catalog | destination |                1
     8053 | export_catalog |             |                0
     8054 | export_catalog |             |                0
(6 rows)

Vertica handles function overloading by displaying the syntax for each implementation. In the previous example, the first implementation (proc_oid 7974) shows two supported parameters: destination and scope; the second implementation (proc_oid 8053) shows only destination.

S3EXPORT Null Handling

S3EXPORT now supports a new null_as parameter, which specifies how to export null values from the source data. If this parameter is included, S3EXPORT replaces all null values with the specified string.

New Meta-function GET_PRIVILEGES_DESCRIPTION

Privileges on database objects can come from several different sources such as explicit grants, roles, and inheritance. New meta-function GET_PRIVILEGES_DESCRIPTION provides a unified view of effective privileges across all sources on a specified database object.

New Helper Functions for Parquet Data

Two new Vertica meta-functions are now available:

GET_METADATA inspects a Parquet file and returns its metadata, including columns, row groups, and sizes. You can use this information to help you define external tables and to confirm the results of exports from Vertica.
INFER_EXTERNAL_TABLE_DDL inspects a Parquet file and returns a starting point for the definition of an external table. This function can be especially helpful for tables with many columns of mostly primitive types. For some types, the function cannot infer the data type and you must edit the output.

COMMENT ON COLUMN Table Compatibility