Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Programming PHP]     [Kernel Newbies]     [PHP Classes]     [Find Someone Nice]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

Add to Google Powered by Linux