Thread: Altering a column type w/o dropping views

I'm going to alter a bunch a tables columns's data type and I'm being
forced to drop a view which depends on the the colum.

eg: ALTER TABLE xs.d_trh ALTER m_dcm TYPE character varying;
ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view v_hpp depends on column "m_dcm"

Is there an alternative method of doing this w/o dropping the existing
view?

On Mon, Jul 07, 2008 at 05:53:58PM +0800, Ow Mun Heng wrote:
> I'm going to alter a bunch a tables columns's data type and I'm being
> forced to drop a view which depends on the the colum.

Why is that a problem?  If you keep your object definitions in files
(e.g., in a directory structure that's under revision control) then
you can write a deployment script like the following (to be executed
via psql):

BEGIN;
DROP VIEW view_name;
ALTER TABLE table_name ALTER column_name TYPE type_name;
\i views/view_name.sql
COMMIT;

--
Michael Fuhr