On 22 Mar 2012, at 10:17, Albe Laurenz wrote:
Or is there a better way of finding view dependencies? I see there's a
pg_catalog entry for tables
that a view depends on but that's not what I'm after.
You can use pg_depend and pg_rewrite as follows:
SELECT DISTINCT r.ev_class::regclass
FROM pg_depend d JOIN
pg_rewrite r ON (d.objid = r.oid)
WHERE d.classid = 'pg_rewrite'::regclass
AND d.refclassid = 'pg_class'::regclass
AND r.ev_class <> d.refobjid
AND d.refobjid::regclass::text LIKE '%myviewname%';
I didn't test it very much, so play around with it a little before
you trust it.
I don't know if it will perform better in your case, but it should
return more appropriate results
(you don't want to find VIEW dummy AS SELECT * FROM t WHERE a =
'myviewname').
Yours,
Laurenz Albe
Thank you - I did come to a similar method yesterday following some pointers from previous messages but I'm glad to have some confirmation it's the right direction. It does perform an order of magnitude faster for me, from 500ms down to under 20ms. My exact query is
SELECT distinct dependent.relname
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class as dependent ON pg_rewrite.ev_class = dependent.oid
JOIN pg_class as dependee ON pg_depend.refobjid = dependee.oid
WHERE dependee.relname = 'myviewname'
AND dependent.relname != 'myviewname'
Haven't tested this much yet either. I'll compare yours to mine and check the differences.
Regards
Oliver