Options
Listing Invalid Views
Jim_Knicely
- Select Field - Administrator
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!
0