Message-ID: <4A0C6405.20609@encs.concordia.ca>
Date: Thu, 14 May 2009 14:33:41 -0400
From: Emi Lu <emilu@encs.concordia.ca>
Reply-To: emilu@encs.concordia.ca
User-Agent: Thunderbird 2.0.0.21 (X11/20090302)
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
CC: =?UTF-8?B?TWFyY2luIFN0xJlwbmlja2k=?= <mstepnicki@gmail.com>
Subject: Re: alter column from varchar(32) to varchar(255) without view
	re-creation
References: <49A411D6.8080909@encs.concordia.ca>
	<179149fe0902240809x2ee73eacy3b684d3761b04b03@mail.gmail.com>
	<49C25CAA.409@encs.concordia.ca>
In-Reply-To: <49C25CAA.409@encs.concordia.ca>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Emi Lu wrote:
>>> PostgreSQL 8.0.15.
>>> Is there a way that I can easily alter column type from varchar(32) to
>>> varchar(255) but do not have to worry about views dependent on it?
> 
>> You should test it carefully and it is considered a bad practice -
>> I'll probably get sued for recommending this :-), but you may try:
>>
>> SELECT * from pg_attribute where attname = 'colname' and  attrelid =
>> (SELECT oid FROM pg_class WHERE relname='_tablename');
>>
>> UPDATE pg_attribute set atttypmod = 4+_new_length_in_your_case_255
>> where attrelid = _attrelid_from_above_ and attname = 'colname';

Tried this, it worked for table but not view.

E.g.,
====================================================
T1(col1 varchar(64)... );
create view v1 as select * from T1;


update pg_attribute set atttypmod = 4+ 128 where ....
\d T1
    col1  varchar(128) ============== [OK]

\d v1
    col1  varchar(64)  ============== [did not change?]

So, it does not really update all dependencies?

Thanks a lot!

--
Lu Ying