Joel Jacobson <joel@xxxxxxxxxxxxxxx> writes: > I'm trying to find all recursive dependecies for an object, using the query > in > http://archives.postgresql.org/pgsql-general/2009-05/msg01192.php Greg pointed out to start with that that query was unpolished (and, in fact, basically untested ...) I modified the query like this: WITH RECURSIVE tree AS ( SELECT 'a'::regclass::text AS tree, 0 AS level, 'pg_class'::regclass AS classid, 'a'::regclass AS objid, 0 as objsubid, ' '::"char" as deptype UNION ALL SELECT tree || ' <-- ' || pg_describe_object(pg_depend.classid, pg_depend.objid, pg_depend.objsubid), level+1, pg_depend.classid, pg_depend.objid, pg_depend.objsubid, pg_depend.deptype FROM tree JOIN pg_depend ON ( tree.classid = pg_depend.refclassid AND tree.objid = pg_depend.refobjid AND (tree.objsubid = pg_depend.refobjsubid OR tree.objsubid = 0)) ) SELECT tree.tree, tree.deptype FROM tree WHERE level < 10 ; and got these results: tree | deptype ------------------------------+--------- a | a <-- rule _RETURN on view a | n a <-- rule _RETURN on view a | i a <-- type a | i a <-- rule _RETURN on view b | n a <-- type a <-- type a[] | i (6 rows) or, starting from b, tree | deptype ------------------------------+--------- b | b <-- rule _RETURN on view b | n b <-- rule _RETURN on view b | i b <-- type b | i b <-- type b <-- type b[] | i (5 rows) which is at least a little bit clearer to look at than what you had. > I ran into problem with view dependencies. The thing you're missing is that implicit dependencies are really bidirectional: you can't delete either object without deleting the other. So you have to scan outwards across reverse implicit dependencies, as well as forward dependencies of all types, if you want to find everything that must be deleted when dropping a given object. I don't immediately see any way to do that with a single recursive query :-(; you'd probably have to code up something in plpgsql. In the case at hand, b's view rule depends normally on a, and also implicitly on b. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
