Exporting objects

The EXPORT_OBJECTS function generates a SQL script that recreates non-virtual catalog objects on a different cluster. It takes arguments for the output file, scope, and K-safety enforcement. For example:

=> SELECT EXPORT_OBJECTS(
     '/home/dbadmin/xtest/sql_objects_all.sql',
     '',       --- what to export (all, in this case)
     'true');  --- enforce K-safety
           EXPORT_OBJECTS
-------------------------------------
 Catalog data exported successfully
(1 row)

If you set the scope to an empty string ('') as in this example, Vertica exports all non-virtual objects to which you have access in order of dependencies. To specify the objects to export, use a comma-delimited list. The function automatically exports objects on which the target objects depend. For example, if you export myschema.mytable, you do not also need to specify myschema.

If you specify a schema, EXPORT_OBJECTS exports all objects in that schema to which you have access. If you name a table that references a sequence, the generated script exports the sequence, then the table that references the sequence, and finally any projections of that table.

Running the generated SQL script on another cluster creates all referenced objects and their dependent objects.

The SQL script:

• Only includes objects to which the user has access.

• Orders CREATE statements based on 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 a KSAFE clause, if any, otherwise with an OFFSET clause.

This function can generate the following types of statements:

• CREATE SCHEMA
• CREATE TABLE

• ALTER TABLE (constraints)

• CREATE PROJECTION
• CREATE VIEW
• CREATE SEQUENCE
• CREATE ACCESS POLICY
• CREATE PROCEDURE (stored)
• CREATE SCHEDULE
• CREATE TRIGGER
• CREATE DATA LOADER

K-safety

By default, the function's KSAFE argument is set to true. In this case, the generated script calls MARK_DESIGN_KSAFE, which replicates K-safety of the original database.

The following example output includes the start and end of an output SQL file:

CREATE SCHEMA store;
CREATE SCHEMA online_sales;
CREATE SEQUENCE public.my_seq ;
CREATE TABLE public.customer_dimension
(
    customer_key int NOT NULL,
    customer_type varchar(16),
    customer_name varchar(256),
    customer_gender varchar(8),
    title varchar(8),
    household_id int,
...

);
...
SELECT MARK_DESIGN_KSAFE(1);

Exporting individual objects

You can specify one or more objects to export in a comma-delimited list. The names of objects in any non-public schema must include their respective schemas.

If listed objects have overlapping scopes—for example, the list includes a table and one of its projections—EXPORT_OBJECTS exports the projection only once:

=> SELECT EXPORT_OBJECTS ('','customer_dimension, customer_dimension_super');

                              export_objects
--------------------------------------------------------------------------
CREATE TABLE public.customer_dimension
(
    customer_key int NOT NULL,
    customer_type varchar(16),
    customer_name varchar(256),
    ...
    CONSTRAINT C_PRIMARY PRIMARY KEY (customer_key) DISABLED
);

CREATE ACCESS POLICY ON public.customer_dimension FOR COLUMN customer_address CASE WHEN enabled_role('administrator') THEN customer_address ELSE '**************' END ENABLE;

CREATE PROJECTION public.customer_dimension_super /*+basename(customer_dimension),createtype(L)*/
(
 customer_key,
 customer_type,
 customer_name,
  ...
)
AS
 SELECT customer_dimension.customer_key,
        customer_dimension.customer_type,
        customer_dimension.customer_name,
        ...
 FROM public.customer_dimension
 ORDER BY customer_dimension.customer_key
SEGMENTED BY hash(customer_dimension.customer_key) ALL NODES OFFSET 0;

SELECT MARK_DESIGN_KSAFE(0);

You can export stored procedures by specifying their names and the types of their formal parameters. For stored procedures with the same name but different formal parameters, you can export all implementations by exporting the parent schema.

To export a particular implementation of a stored procedure, specify either the types or both the names and types of the procedure's formal parameters. The following example specifies the types:

=> SELECT EXPORT_OBJECTS('','raiseXY(int, int)');
    EXPORT_OBJECTS
----------------------

CREATE PROCEDURE public.raiseXY(x int, y int)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';

SELECT MARK_DESIGN_KSAFE(0);

(1 row)

To export all implementations of an overloaded stored procedure, export its parent schema:

=> SELECT EXPORT_OBJECTS('','public');
    EXPORT_OBJECTS
----------------------

...

CREATE PROCEDURE public.raiseXY(x int, y varchar)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';

CREATE PROCEDURE public.raiseXY(x int, y int)
LANGUAGE 'PL/vSQL'
SECURITY INVOKER
AS '
BEGIN
RAISE NOTICE ''x = %'', x;
RAISE NOTICE ''y = %'', y;
-- some processing statements
END
';

SELECT MARK_DESIGN_KSAFE(0);

(1 row)

See also