How To replace view : too long view_definition

yamakawa

Hi.
Please tell me how to fix it.
1. Create View
CREATE VIEW public.VIEW_A AS SELET col1 as COLUMN1, ,,, col1000 AS COLUMN1000 FROM public.TABLE_A WHERE col1='20200801';
2. select views
SELECT * FROM v_catalog.views where table_name='VIEW_A';
-> The definition is too long and is interrupted in the middle.

To replace view, I want to get view_definition from views.(And replace where condition)
But can't.

We await answers from the experts.

Jim_Knicely

Use EXPORT_OBJECTS.

SELECT export_objects('', 'VIEW_A'); -- To the screen
SELECT export_objects('/home/dbadmin/view_a.sql'', 'VIEW_A'); -- To a file

Doc Page:

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

Helpful tip:
https://www.vertica.com/blog/export-create-or-replace-ddl-for-database-views/