On 20 February 2012 17:29, hubert depesz lubaczewski <dep...@depesz.com> wrote: > On Mon, Feb 20, 2012 at 01:06:29PM +0000, Thom Brown wrote: >> You could try this: >> >> SELECT distinct dependee.relname >> FROM pg_depend >> JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid >> JOIN pg_class as dependee ON pg_rewrite.ev_class = dependee.oid >> JOIN pg_class as dependent ON pg_depend.refobjid = dependent.oid >> JOIN pg_attribute ON pg_depend.refobjid = pg_attribute.attrelid >> AND pg_depend.refobjsubid = pg_attribute.attnum >> WHERE dependent.relname = <tablename> >> AND pg_attribute.attnum > 0 >> AND pg_attribute.attname = <columnname>; > > thanks. took me a while to understand it, so decided to make it a bit > shorter, and change the join order to the order of data flow: > > SELECT > distinct r.ev_class::regclass > FROM > pg_attribute as a > join pg_depend as d on d.refobjid = a.attrelid AND d.refobjsubid = > a.attnum > join pg_rewrite as r ON d.objid = r.oid > WHERE > pg_attribute.attrelid = '<table name>'::regclass > AND pg_attribute.attname = '<column name>'; > > but the logic in here is the same as in your query.
Yes, regclass will allow you to take a couple shortcuts and I'm not sure why I didn't do that. You'd need to correct your WHERE clause though to use the 'a' alias you created. I'd imagine that if you were going to use such a query regularly, you'd need to add some extra considerations into it to ensure you're not matching anything incorrectly. I only say this because I hadn't really put too much thought into the query. I don't know if it may inadvertently match non-view objects. Glad it helped in some way though. -- Thom -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
