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

On 20 February 2012 17:29, hubert depesz lubaczewski <depesz@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