
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!