Re: 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(at)gmx(dot)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(at)gmx(dot)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/