Re: AW: ALTER TABLE DROP COLUMN

KuroiNeko wrote:

> Inoue san,
>
> > This style of "DROP COLUMN" would change the attribute
> > numbers whose positons are after the dropped column.
> > Unfortunately we have no mechanism to invalidate/remove
> > objects(or prepared plans) which uses such attribute numbers.
>
> 1 create table alpha( id int4, payload text );
> 2 insert into alpha( id, payload ) values( 0, 'zero' );
> 3 create table t( payload text );
> 4 insert into t( payload ) select payload from alpha;
> 5 drop table alpha;
> 6 alter table t rename to alpha;
>
> Not a big deal, right?

Yes,there's a similar procedure in FAQ.

> Also, drop column isn't really needed
> that often and requires alot of manual processing, like updating
> views/rules/procedures etc.

The FAQ doesn't refer to alot of manual processing at all.
Certainly it's very difficult to cover all procederes to
accomplish "DROP COLUMN". It's one of the reason why
I've said "DROP COLUMN" isn't that easy.

>
> On the other hand, when dropping a column (multiple columns) in a table
> with 10+ columns, statements 3 and 4 above may become quite painfull.
> It'd be nice if drop column were `expanded' to appropriate queries
> automatically. Not sure about abovementioned attribute numbers in such
> case.
> In general, however, if drop column is the only statement that is likely
> to affect attribute numbers this way (assuming that add column always adds
> and never inserts an attribute), then a fairly simple function in plpgsql,
> shipped with template1 will probably do.

plpgsql functions are executed in a transaction.
I don't think plpgsql could execute
"insert(select into) -> drop -> rename"
properly(at least currently).

Regards.

Hiroshi Inoue

> At least it should work to drop a
> single column, because full-featured function will require argument list of
> variable length.
>
> Ed
>
> ---
> Well I tried to be meek
> And I have tried to be mild
> But I spat like a woman
> And I sulked like a child
> I have lived behind the walls
> That have made me alone
> Striven for peace
> Which I never have known
>
> Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)