The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Listing Invalid Views

If any of the tables referenced in a view are dropped, the view becomes invalid.

Example:

dbadmin=> CREATE TABLE base_table (c INT);
CREATE TABLE

dbadmin=> INSERT INTO base_table SELECT 1;
OUTPUT
--------
      1
(1 row)

dbadmin=> CREATE VIEW  base_table_vw AS SELECT c FROM base_table;
CREATE VIEW

dbadmin=> SELECT * FROM base_table_vw;
c
---
1
(1 row)

dbadmin=> DROP TABLE base_table;
DROP TABLE

dbadmin=> SELECT * FROM base_table_vw;
ERROR 4568:  Relation "public.base_table" does not exist

You can list all of the invalid views in your database by passing a bogus search string (i.e. a view that does not exist) to the \dv vsql meta command!

Example:

dbadmin=> \dv this_is_a_bogus_search_string
WARNING 3791:  Invalid view base_table_vw: Relation "public.base_table" does not exist
No matching relations found.

Have fun!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.