Export CREATE OR REPLACE DDL for Database Views

Posted July 19, 2019 by James Knicely, Vertica Field Chief Technologist

Data Definition Language in red 3D text in a field of white 3D ones and zeros
The EXPORT_OBJECTS Catalog Management Function generates a SQL script you can use to recreate non-virtual catalog objects on another cluster.

When you export a database view, Vertica includes the CREATE VIEW DDL in the output. But what if you want the DDL to be CREATE OR REPLACE VIEW?

You can make that change with the Linux sed command.

Example: dbadmin=> CREATE TABLE base_table (c1 INT, c2 INT, c3 INT); CREATE TABLE dbadmin=> CREATE VIEW base_table_vw AS SELECT * FROM base_table; CREATE VIEW dbadmin=> SELECT export_objects('', 'base_table_vw', FALSE); export_objects ---------------------------------------------------------------------------------------------------------------------------------------- CREATE VIEW public.base_table_vw AS SELECT base_table.c1, base_table.c2, base_table.c3 FROM public.base_table; (1 row) dbadmin=> \! vsql -c "SELECT export_objects('', 'base_table_vw', FALSE);" | sed "s/CREATE VIEW/CREATE OR REPLACE VIEW/" export_objects ---------------------------------------------------------------------------------------------------------------------------------------- CREATE OR REPLACE VIEW public.base_table_vw AS SELECT base_table.c1, base_table.c2, base_table.c3 FROM public.base_table; (1 row) Helpful Link:

https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/VerticaFunctions/EXPORT_OBJECTS.htm

Have fun!