Listing Invalid Views

Jim_Knicely

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!