EXPORT_CATALOG

This function and EXPORT_OBJECTS return equivalent output.

Generates a SQL script for recreating a physical schema design on another cluster.

The SQL script conforms to the following requirements:

  • Only includes objects to which the user has access.

  • Orders CREATE statements according to object dependencies so they can be recreated in the correct sequence. For example, if a table is in a non-PUBLIC schema, the required CREATE SCHEMA statement precedes the CREATE TABLE statement. Similarly, a table's CREATE ACCESS POLICY statement follows the table's CREATE TABLE statement.

  • If possible, creates projections with their KSAFE clause, if any, otherwise with their OFFSET clause.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

EXPORT_CATALOG ( ['[destination]' [, '[scope]']] )

Parameters

destination
Specifies where to send output, one of the following:
  • An empty string ('') writes the script to standard output.

  • The path and name of a SQL output file. This option is valid only for superusers. If you specify a file that does not exist, the function creates one. If you specify only a file name, Vertica creates it in the catalog directory. If the file already exists, the function silently overwrites its contents.

scope
Determines what to export. Within the specified scope, EXPORT_CATALOG exports all the objects to which you have access:
  • DESIGN (default): Exports all catalog objects: schemas, tables, constraints, views, access policies, projections, SQL macros, and stored procedures.

  • DESIGN_ALL: Deprecated

  • TABLES: Exports all tables and their access policies. See also EXPORT_TABLES.

  • DIRECTED_QUERIES: Exports all directed queries that are stored in the database. For details, see Managing directed queries.

Privileges

None

Examples

See Exporting the catalog.

See also