modifying columns which are included in a view fails

[Ferdinand]

Example

DETAIL: cannot alter type of a column used by a view or rule DETAIL: rule _RETURN on view hr_expense_report depends on column "unit_amount"

IMHO if this type of error occurs the dependent view must be deleted using
- tools.drop_view_if_exists(cr, 'hr_expense_report')
and the operation must be reexecuted to allow clean automated installation

otherwise the
- tools.drop_view_if_exists(cr, 'hr_expense_report')
has to be included in a module which is installed independant of the one creating the view.
which has some advantages for the devloper to know but

[Ferdinand (office-chricar)]

.... but this is not a general vaild solution, because no module developer can know about all views installed in a system.
but I am not sure that a view deleted this way will be reinstalled automatically

[Dhruti Shastri(OpenERP) (dhs-openerp)]

Hello Dr. Ferdinand,

would you please elaborate more regarding this issue.

I am not getting you exactly regarding field alteration.

Awaiting for the reply.

[Ferdinand (office-chricar)]

it's a general postgres problem
if a column is used in a view it can't be modified

Example
psql (9.0.3)

create table test (col_1 varchar(64));

create view test_view as select col_1 from test;

alter table test alter col_1 type varchar(128);
ERROR: cannot alter type of a column used by a view or rule
DETAIL: rule _RETURN on view test_view depends on column "col_1"

More and more columns are used in views used by analytics and reports hence it becomes more and more cumbersome or even impossible to write modules which must change column length or precision.

a possible solution for length is described here
http://sniptools.com/databases/resize-a-column-in-a-postgresql-table-without-changing-data

SELECT atttypmod FROM pg_attribute
WHERE attrelid = 'test'::regclass
AND attname = 'col_1';
 atttypmod
-----------
        68

 UPDATE pg_attribute SET atttypmod = 68+64
WHERE attrelid = 'test'::regclass
AND attname = 'col_1';

\d test
             Table "public.test"
 Column | Type | Modifiers
--------+------------------------+-----------
 col_1 | character varying(128) | not null

\d test_view
          View "public.test_view"
 Column | Type | Modifiers
--------+-----------------------+-----------
 col_1 | character varying(64) |

UPDATE pg_attribute SET atttypmod = 68+64
WHERE attrelid = 'test'::regclass
AND attname = 'col_1';

postgres=# \d test_view
           View "public.test_view"
 Column | Type | Modifiers
--------+------------------------+-----------
 col_1 | character varying(128) |

 insert into test values ('1234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890');

select length(col_1) from test_view;
 length
--------
    100

probalby a similar solution can be found for precision or numeric types

[Ferdinand (office-chricar)]

sorry I copied one wrong statement to modify the view description

UPDATE pg_attribute SET atttypmod = 68+64
WHERE attrelid = 'test_view'::regclass
AND attname = 'col_1';

[Dhruti Shastri(OpenERP) (dhs-openerp)]

Hello Dr.Ferdinand,

Thanks a lot for your nice explanation. It is good feature to improve.

However it is not a bug so currently I am setting it as wish-list or you can also post your feedback at feedback.openerp.com. Our Quality team member will definitely review it.

Thanks.