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

From hubert depesz lubaczewski
Subject Re: How can I get list of views that are using given column in table?
Date
Msg-id 20120220172950.GA9813@depesz.com
Whole thread Raw
In response to Re: How can I get list of views that are using given column in table?  (Thom Brown <thom@linux.com>)
Responses Re: How can I get list of views that are using given column in table?
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/

pgsql-general by date:

Previous
From:
Date:
Subject: Re: Question on Rules
Next
From: Thom Brown
Date:
Subject: Re: How can I get list of views that are using given column in table?