Re: Simple Column reordering

Simon Riggs wrote:
> On Fri, 2007-02-23 at 11:25 +0100, Peter Eisentraut wrote:
>   
>> Am Freitag, 23. Februar 2007 09:08 schrieb Simon Riggs:
>>     
>>> If this is standards-breaking as you say, I would withdraw immediately.
>>> I checked the SQL standard and could not see how this would do so. The
>>> standard states SELECT * would return columns in order; it doesn't say
>>> what that order should be,
>>>       
>> b) Otherwise, the <select list> “*” is equivalent to a <value expression> 
>> sequence in which each <value expression> is a column reference that 
>> references a column of T and each column of T is referenced exactly once. The 
>> columns are referenced in the ascending sequence of their ordinal position 
>> within T.
>>     
>
> Which begs the question: what is their ordinal position? If we change
> the ordinal position at CREATE TABLE time then the SELECT * would still
> work per standard.
>
>   

That's quite a stretch. Surely "their ordinal position" can't mean 
"their ordinal position as arbitrarily determined at CREATE TABLE time 
by the implementation".

I really don't think that we can accept under any circumstances a 
situation where something as simple as this breaks:
 create table foo (x text, y int); insert into foo values ('qwerty',1);

Physical storage optimization must not have any SQL level visibility or 
consequences, IMNSHO, regardless of what we do about providing mutable 
display order.

If you really want an interim solution, what about a builtin function 
that would explicitly mutate the definition and table contents (if any) 
along the lines you want? (assuming that's lots less work than just 
doing the whole thing right to start with). Or even one which just 
*displayed* the optimal order might be sufficient assistance to DBAs who 
want to take advantage of this.

cheers

andrew