Thread: ERROR: cannot alter type of a column used by a view or rule

---

Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador do Yahoo! agora.

On Wed, 16 Feb 2005, Daniel Naschenweng wrote:

> I create this table:
> create table teste (campo1 varchar(20));
>
> And this view:
> create view vteste as select * from teste;
>
> When I try change the type of the column raise thir error:
> db=# alter table teste alter column campo1 type varchar(30);
> ERROR: cannot alter type of a column used by a view or rule
> DETAIL: rule _RETURN on view vteste depends on column "campo1"
>
> My question is: Who I can create a "select" no system catalog to return
> views with this column befor raise error?

I haven't really tested it much, but I think something like the following
will work to give you the pg_class row for a view dependant on a
particular column.

select pg_class.* from pg_depend,pg_attribute,pg_rewrite,pg_class where
refclassid=1259 and
refobjid='teste'::regclass and
pg_attribute.attrelid='teste'::regclass and
refobjsubid = attnum and
attname='campo1' and
classid='pg_rewrite'::regclass and
pg_rewrite.oid=objid and
pg_rewrite.rulename='_RETURN' and
pg_class.oid=pg_rewrite.ev_class;

On Wed, 16 Feb 2005, Daniel Naschenweng wrote:

> I create this table:
> create table teste (campo1 varchar(20));
>
> And this view:
> create view vteste as select * from teste;
>
> When I try change the type of the column raise thir error:
> db=# alter table teste alter column campo1 type varchar(30);
> ERROR: cannot alter type of a column used by a view or rule
> DETAIL: rule _RETURN on view vteste depends on column "campo1"
>
> My question is: Who I can create a "select" no system catalog to return
> views with this column befor raise error?

I haven't really tested it much, but I think something like the following
will work to give you the pg_class row for a view dependant on a
particular column.

select pg_class.* from pg_depend,pg_attribute,pg_rewrite,pg_class where
refclassid=1259 and
refobjid='teste'::regclass and
pg_attribute.attrelid='teste'::regclass and
refobjsubid = attnum and
attname='campo1' and
classid='pg_rewrite'::regclass and
pg_rewrite.oid=objid and
pg_rewrite.rulename='_RETURN' and
pg_class.oid=pg_rewrite.ev_class;

---

Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador do Yahoo! agora.