Finding recursive dependencies

Hi,

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

I ran into problem with view dependencies.

In the example below, the view "b" depends on view "a".

How is it possible to interpret the result of the pg_depend query below,
to detect it's possible to drop "b", but dropping "a" is not possible, since
it depends on "b"?

Non of the objid or refobjid in pg_depend contain the oids of the views,
192092 nor 192096.
The view oids appears to be stored in pg_rewrite.ev_class though, but I find
it strange I cannot find them in pg_depend?

I'm sure there is an explanation to this and a simple way to solve my
problem.

How can a general query be constructed to find out all dependencies for any
given oid, regardless of its object class, listing all objects it depends
on recursively, or alternatively, listing all objects depending on the given
object recursively?

It would be best if such a query could be constructed only using pg_depend
and pg_class, without involving class specific tables such as pg_rewrite,
pg_constraint etc, as such a join would be quite expensive and
"non-general".

test=# CREATE VIEW a AS SELECT 1;
test=# CREATE VIEW b AS SELECT * FROM a;
test=# SELECT oid, relname FROM pg_class WHERE relname IN ('a','b');
oid | relname
--------+---------
192092 | a
192096 | b
(2 rows)

test=# WITH RECURSIVE tree AS (
test(# SELECT 'a'::regclass::text AS tree,
test(# 0 AS level,
test(# 'pg_class'::regclass AS classid,
test(# 'a'::regclass AS objid
test(# UNION ALL
test(# SELECT tree ||
test(# ' <-- ' ||
test(# pg_depend.classid::regclass || ' ' || pg_depend.objid ||
' ' || pg_depend.deptype,
test(# level+1,
test(# pg_depend.classid,
test(# pg_depend.objid
test(# FROM tree
test(# JOIN pg_depend ON ( tree.classid = pg_depend.refclassid
test(# AND tree.objid = pg_depend.refobjid)
test(# )
test-# SELECT tree.tree
test-# FROM tree
test-# WHERE level < 10
test-# ;
tree
---------------------------------------------
a
a <-- pg_rewrite 192095 n
a <-- pg_rewrite 192095 i
a <-- pg_type 192094 i
a <-- pg_rewrite 192099 n
a <-- pg_type 192094 i <-- pg_type 192093 i
(6 rows)

-- Same query for b:
tree
---------------------------------------------
b
b <-- pg_rewrite 192099 n
b <-- pg_rewrite 192099 i
b <-- pg_type 192098 i
b <-- pg_type 192098 i <-- pg_type 192097 i
(5 rows)

--
Best regards,

Joel Jacobson
Glue Finance