Search code examples
sqlvertica

Querying a view with a long view_defintion column (over 32,000 chars) is returned cut of at the end

Lets say for the sake of brevity, I have a long column definition for a column (MyLongColumn), here is the defintion:

CREATE VIEW public.MyView AS
SELECT 'A long long view defintion...' ::varchar(40152) AS MyLongColumn
FROM public.MyTable;
SELECT MARK_DESIGN_KSAFE(0);

Now, My goal is the query the MyView's view_defintion with

select * from VIEWS where  UPPER(TABLE_SCHEMA) not in ( UPPER('V_MONITOR'),UPPER('V_CATALOG'))

The only problem is that the view_definition column returns cut of at the end (with only 32,354 chars instead of 40152).

I cannot change the view definition (A Client), any help will be appreciated.

Solution

  • The short answer is: you can't with the current Vertica setup.

    The v_catalog.views system is defined like so:

    CREATE TABLE v_catalog.views (
  table_schema_id     INT        
, table_schema        VARCHAR(128)  
, table_id            INT        
, table_name          VARCHAR(128)  
, owner_id            INT        
, owner_name          VARCHAR(128)  
, view_definition     VARCHAR(32000)
, is_system_view      BOOLEAN       
, system_view_creator VARCHAR(128)  
, create_time         TIMESTAMP(6)  
, is_local_temp_view  BOOLEAN       
, inherit_privileges  BOOLEAN       
)
;

    This is what the Vertica platform foresees for the storage of view definition DDL.

    In general, I discourage Vertica users from creating all too complex views. Much of it can be implemented much more efficiently by the use of:

    • Flattened Tables - if the view is a pre-join view
    • Data Access Policies - for row-wise and column-wise data visibility filters.

    Check out this part of the docu for Flattened Tables: https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/AnalyzingData/FlattenedTables/FlattenedTables.htm?zoom_highlight=flattened%20table

    .. and this part for Data Access Policies: https://www.vertica.com/docs/10.1.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEACCESSPOLICY.htm?zoom_highlight=data%20access%20policy