Is view valid or invalid?

[harryrundles]

Hi guys,

Is there a way to systematically check if a view is valid or invalid? In Oracle I've used the STATUS column in the ALL_OBJECTS system table to determine this. I can't find a similar column in Vertica.

Code: Select all

dbadmin=> create table test (id int, name varchar(10));
CREATE TABLE
dbadmin=> create view test_view as select * from test;
CREATE VIEW
dbadmin=> select * from test_view;
 id | name 
----+------
  1 | Me
(1 row)

dbadmin=> drop table test;
DROP TABLE
dbadmin=> select * from test_view;
ERROR 4568:  Relation "public.test" does not exist

The view should be invalid since I dropped the base table.

[JimKnicely]

In vsql if you use the \dv meta command and pass in a bogus search string (i.e. a view that does not exist), you'll get a list of invalid views:

Code: Select all

dbadmin=> \dv jim
WARNING 3791:  Invalid view person_cases2_vw: Relation "sales.person_vw" does not exist
WARNING 3791:  Invalid view person_cases2_vw: Relation "sales1.person_vw" does not exist
WARNING 3791:  Invalid view person_cases2_vw: Relation "sales2.person_vw" does not exist
WARNING 3791:  Invalid view person_cases2_vw: Relation "sales3.person_vw" does not exist
No matching relations found.
dbadmin=>