Re: is there any query so that I may find the list of columns those have rules? - Mailing list pgsql-general

AI Rumman wrote:
> I am getting the following error:
> ALTER TABLE base_table ALTER COLUMN base_table_field1 TYPE
numeric(10,6);
> ERROR:  cannot alter type of a column used by a view or rule
> DETAIL:  rule _RETURN on view master_view depends on column
"base_table_field1"
>
> I know that I have a view based in the "base_table_field1" column.
>
> So is there any query so that I may find the list of columns those
have rules?

Does this help:

SELECT d1.refobjid::regclass AS table,
       a.attname AS column,
       d2.refobjid::regclass AS "referenced by view"
FROM pg_depend d1
JOIN pg_depend d2 ON (d1.objid = d2.objid
                AND d1.classid = 'pg_rewrite'::regclass
                AND d1.refclassid ='pg_class'::regclass
                AND d2.classid = 'pg_rewrite'::regclass
                AND d2.refclassid = 'pg_class'::regclass
                AND d2.deptype='i')
JOIN pg_attribute a ON (d1.refobjid = a.attrelid
                AND d1.refobjsubid = a.attnum)
WHERE d1.refobjid='base_table'::regclass
ORDER BY 1, 2, 3;

Yours,
Laurenz Albe