Re: How do I find what views depend on a particular table? - Mailing list pgsql-general

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