From: | hubert depesz lubaczewski <depesz(at)depesz(dot)com> |
---|---|
To: | Thom Brown <thom(at)linux(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: How can I get list of views that are using given column in table? |
Date: | 2012-02-20 17:29:50 |
Message-ID: | 20120220172950.GA9813@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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/
From | Date | Subject | |
---|---|---|---|
Next Message | Thom Brown | 2012-02-20 17:45:31 | Re: How can I get list of views that are using given column in table? |
Previous Message | mgould | 2012-02-20 17:07:18 | Re: Question on Rules |