Thread: How do I find what views depend on a particular table?

I've found the pg_depend table, but it doesn't appear to include this info.
(Or am I missing it?)

I'm looking to get all view names that I need to drop to allow a particular
table to DROP RESTRICT.

Alternately, I would be happy with a way to get the names of objects that
are dropped when DROP CASCADE is used.


-------------------------------------------
justin tocci
Fort Wayne, IN

Justin Tocci <jtocci@tlcusa.com> writes:
> I've found the pg_depend table, but it doesn't appear to include this info.
> (Or am I missing it?)

You're missing it.

IIRC, the dependency is actually from the view's ON SELECT rule to the
tables (and other objects) mentioned in the rule.  There is also a
dependency from the ON SELECT rule to the view itself.  This latter
dependency is marked INTERNAL, which (a) prevents you from dropping the
ON SELECT rule by itself, and (b) tells a DROP CASCADE that reaches the
rule to cascade up to the view.  See the discussion at
http://developer.postgresql.org/docs/postgres/catalog-pg-depend.html

> Alternately, I would be happy with a way to get the names of objects that
> are dropped when DROP CASCADE is used.

You can do

BEGIN;
DROP foo CASCADE;
ROLLBACK;

and examine the set of NOTICEs emitted by the DROP.  This is perhaps not
very amenable to mechanization :-(

            regards, tom lane