Re: How can I get list of views that are using given column in table? - Mailing list pgsql-general

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.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/