Thread: SQL92 compliance
Hi, Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92 compliant? PostgreSQL requires this keyword by default when defining alias, which might be good thing. I mean, I would prefer being notified from a syntax error than spending a couple of hours wondering why a client application, at the end of a several data marshalling processes, doesn't get the right data... For instance MySQL doesn't require it and sometimes that sucks: SELECT my_column1 my_column2, my_column3 FROM my_table Regards, -- Daniel
On Aug 23, 2006, at 10:40 , Daniel CAUNE wrote: > Hi, > > Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to > be SQL92 > compliant? You can find this in the documentation: http://www.postgresql.org/docs/8.1/interactive/sql-select.html#AEN48391 > The AS Key Word > > In the SQL standard, the optional key word AS is just noise and can > be omitted without affecting the meaning. The PostgreSQL parser > requires this key word when renaming output columns because the > type extensibility features lead to parsing ambiguities without it. > AS is optional in FROM items, however. Hope this helps. Michael Glaesemann grzm seespotcode net
Am Mittwoch, 23. August 2006 03:40 schrieb Daniel CAUNE: > Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92 > compliant? No. I have a patch at <http://developer.postgresql.org/~petere/select-without-as/select-without-as.patch> that fixes this at least for 7.4. I don't think it works for newer versions, but it should give you an idea what is required to get it working. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut <peter_e@gmx.net> writes: > Am Mittwoch, 23. August 2006 03:40 schrieb Daniel CAUNE: >> Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92 >> compliant? > No. I have a patch at > <http://developer.postgresql.org/~petere/select-without-as/select-without-as.patch> > that fixes this at least for 7.4. I think it's a big stretch to say that that patch fixes it, since it only allows an AS-less target expression to be c_expr rather than a_expr as it ought to. The problem is really insoluble given that we allow user-defined postfix operators: is "SELECT x ~~ y" meant to be an infix operator with arguments x and y, or a postfix operator with argument x and a column label y? When this has come up in the past, we've always concluded that compliance with this not-very-well-thought-out detail of the spec is not worth the price of giving up postfix operators. Even if we were willing to do that, I think we'd also have to give up using bison to generate the parser :-( because some constructs would require more than one-token lookahead. regards, tom lane
On Wed, 2006-08-23 at 12:40, Tom Lane wrote: > Peter Eisentraut <peter_e@gmx.net> writes: > > Am Mittwoch, 23. August 2006 03:40 schrieb Daniel CAUNE: > >> Is AS in "SELECT my_column AS my_name FROM my_table" mandatory to be SQL92 > >> compliant? > > > No. I have a patch at > > <http://developer.postgresql.org/~petere/select-without-as/select-without-as.patch> > > that fixes this at least for 7.4. > > I think it's a big stretch to say that that patch fixes it, since it > only allows an AS-less target expression to be c_expr rather than > a_expr as it ought to. > > The problem is really insoluble given that we allow user-defined > postfix operators: is "SELECT x ~~ y" meant to be an infix operator > with arguments x and y, or a postfix operator with argument x and > a column label y? > > When this has come up in the past, we've always concluded that > compliance with this not-very-well-thought-out detail of the spec > is not worth the price of giving up postfix operators. > > Even if we were willing to do that, I think we'd also have to give > up using bison to generate the parser :-( because some constructs > would require more than one-token lookahead. Would it be possible if we required postfix operators and related to be inside parens? select x ~~ y as yabba OR select (x ~~ y) yabba Not that I'd want that. I prefer it the way it is too. Just more of an intellectual exercise.
Tom Lane wrote: > I think it's a big stretch to say that that patch fixes it, since it > only allows an AS-less target expression to be c_expr rather than > a_expr as it ought to. Well, it works for a useful subset. I remember that some other database did not *allow* the AS, so there were porting troubles, but I don't remember the details. I'm just saying that it's doable if it's a necessity for someone. -- Peter Eisentraut http://developer.postgresql.org/~petere/
Scott Marlowe wrote: > Would it be possible if we required postfix operators and related to > be inside parens? > > select x ~~ y as yabba > OR > select (x ~~ y) yabba That's pretty much what you get if you restrict the expression to c_expr. -- Peter Eisentraut http://developer.postgresql.org/~petere/