Is there an easy way to drop a view that is based on a table when that table is dropped?
If I have a simple view V based on a table T, when I drop table T I would like the view V to automatically be dropped!
Code: Select all
dbadmin=> CREATE TABLE t as SELECT * FROM dual;
CREATE TABLE
dbadmin=> CREATE VIEW v AS SELECT * FROM t;
CREATE VIEW
dbadmin=> SELECT * FROM views WHERE table_name = 'v';
-[ RECORD 1 ]-------+-----------------------------------
table_schema_id | 45035996273769528
table_schema | sales_wh
table_id | 45035996283979490
table_name | v
owner_id | 45035996273704962
owner_name | dbadmin
view_definition | SELECT t.dummy FROM sales_wh.t
is_system_view | f
system_view_creator |
create_time | 2013-02-15 13:12:01.607654-05
dbadmin=> DROP TABLE t CASCADE;
DROP TABLE
dbadmin=> SELECT * FROM views WHERE table_name = 'v';
-[ RECORD 1 ]-------+-----------------------------------
table_schema_id | 45035996273769528
table_schema | sales_wh
table_id | 45035996283979490
table_name | v
owner_id | 45035996273704962
owner_name | dbadmin
view_definition | SELECT t.dummy FROM sales_wh.t
is_system_view | f
system_view_creator |
create_time | 2013-02-15 13:12:01.607654-05
dbadmin=> SELECT * FROM v;
ERROR 4568: Relation "sales_wh.t" does not exist