Skip to main content
Question

How To replace view : too long view_definition

  • August 21, 2020
  • 1 reply
  • 6 views

yamakawa
Forum|alt.badge.img+1

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.

1 reply

Jim_Knicely
Forum|alt.badge.img+2
  • Participating Frequently
  • August 21, 2020

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/