Re: Alter column with views depended on it without drop views

On Wed, Mar 19, 2014 at 11:27 PM, Emi Lu <emilu@encs.concordia.ca> wrote:
> Is there a way to change a table column from varchar(n) to varchar with
> views depended on it.
Nope. You cannot update the data type of a table column if it is used by a view.
=# create table aa (a varchar(4));
CREATE TABLE
=# create view aav as select * from aa;
CREATE VIEW
Time: 13.605 ms
=# alter table aa alter column a set data type varchar;
ERROR:  0A000: cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view aav depends on column "a"
LOCATION:  ATExecAlterColumnType, tablecmds.c:7814

> The problem is that I do not want to drop and re-create all views
> dependencies. I am looking for a way/command that can change the column type
> and all views will be auto updated as well.
A solution to minimize the impact on users would be to do all those
operations inside the same transaction:
begin;
drop view v1;
[...]
alter table tab alter column col set data type mytype;
create view v1 as select blabla;
[...]
commit;
Regards,
--
Michael