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

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