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

hi
I have situation, where I need to change datatype of column.
But when I do:
alter table xx alter column yy type zz;
i get error:
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view some_view depends on column "yy"

how can I get a list of all such views (in a sqlish way, so I could make a
query to return all needed objects that need to be dropped/recreated).

Best regards,

depesz

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

On 20 February 2012 12:06, hubert depesz lubaczewski <depesz@depesz.com> wrote:
> hi
> I have situation, where I need to change datatype of column.
> But when I do:
> alter table xx alter column yy type zz;
> i get error:
> ERROR:  cannot alter type of a column used by a view or rule
> DETAIL:  rule _RETURN on view some_view depends on column "yy"
>
> how can I get a list of all such views (in a sqlish way, so I could make a
> query to return all needed objects that need to be dropped/recreated).

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>;

--
Thom

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/

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