Ernest E Vogelsinger wrote:
> At 07:55 16.06.2003, Jim C. Nasby said:
>
>>99.999% of the time, if you put SELECT * into code, you should be strung
>>up by your own entrails. But do you mean to tell me that when you're
>>testing stuff on the command line you never, ever use SELECT *?
>
> Sure I do. But I don't really care about column ordering when doing a
> SELECT * from the psql command line. If I care I usually have a dummy file
> named "x" (because that's so damn short ;->), hack in my complex test
> queries there, and do a \i x in psql. If I just want to see if something's
> there (or not) I'm not interected in column order. Are you?
>
> Basically as I understand it, SELECT * means "gimme all", not in any
> particular order. How if at all is that defined in ANSI SQL?
SQL92:
7.9 <query specification>
Syntax Rules
1) Let T be the result of the <table expression>.
2) The degree of the table specified by a <query specification> is
equal to the cardinality of the <select list>.
3) Case:
a) If the <select list> "*" is simply contained in a <subquery> that
is immediately contained in an <exists predicate>, then the <select
list> is equivalent to a <value expression> that is an arbitrary
<literal>.
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.
It's that last sentence:
"The columns are referenced in the ascending sequence of their ordinal
position within T."
As Tom pointed out earlier, it may not make sense purely from a
relational point of view, but it is required by the standard. Given
that it is required, it would be nice if the user could modify the
ordinal position within T.
Mike Mascari
mascarm@mascari.com