Re: RE : full featured alter table?

=?ISO-8859-1?Q?Sven_K=F6hler?= <skoehler@upb.de> writes:
> perhaps we could also think about a
> ALTER TABLE <table> POSITIONS <column1>,<column2>,...

You could invent a syntax that supports both use cases, along the lines
of

ALTER ... POSITION <i> <column1> [ , <column2> ... ]

with the meaning that the named columns are inserted sequentially between
positions i-1 and i, moving them from wherever they were, and leaving
all not-mentioned columns in their existing relative order.  This
degenerates to the same as your first proposal if one column is named,
and at the other extreme allows all the columns to be re-ordered in one
command.

It could get a little confusing if some of the named columns previously
occupied positions less than <i>.  I'd suggest the following
more-concrete specification:

1. <i> must be in the range 1 to (<number of columns in table> -
   <number of columns named in statement> + 1).
2. After the ALTER, the named columns have ordinal positions <i>, <i+1>,
   etc.
3. Any columns not named are placed into the remaining slots (1..i-1
   and i+nnamedcols..ntablecols) in the same relative order they had
   before.

            regards, tom lane