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

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