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

On 20 February 2012 12:06, hubert depesz lubaczewski <depesz(at)depesz(dot)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