Thread: EXTRACT Clarification
Switching this thread to DOCS and renaming it... Anyway, I think that this situation calls for some clarification in the docs. If others agree, I'd be happy to submit a potential patch. I'm thinking something like this (with thanks to Stephan): Note: EXTRACT is not a true function. SQL defines it as an expression that happens to look similar to a function call. Is this wording acceptable? I'd imagine putting it at the end of 9.8.1. Also, are there other expressions that fall into this category? I don't know the spec well enough to know. -tfo On Sep 29, 2004, at 1:30 AM, Stephan Szabo wrote: > sszabo@bigpanda.com > > On Tue, 28 Sep 2004, Thomas F.O'Connell wrote: > >> From 11.5 in the docs: >> >> "The syntax of the CREATE INDEX command normally requires writing >> parentheses around index expressions, as shown in the second example. >> The parentheses may be omitted when the expression is just a function >> call, as in the first example." >> >> But when I try this: >> >> db=# CREATE INDEX expression_idx on some_table( extract( year from >> some_column ) ); > > Extract(year from some_column) is not really just a function call it's > an expression that looks similar to a function call because that's how > SQL > defined it.
On Wed, 29 Sep 2004, Thomas F.O'Connell wrote: > Note: EXTRACT is not a true function. SQL defines it as an expression > that happens to look similar to a function call. > > Also, are there other expressions that fall into this category? I don't > know the spec well enough to know. At least COALESCE and NULLIF are not functions. Kris Jurka
It seems like it would be worth noting these (and any others) in the docs in some way. Is there a way for someone without a copy of the spec to be aware of which are functions and which are not, otherwise? -tfo On Sep 29, 2004, at 9:25 AM, Kris Jurka wrote: > On Wed, 29 Sep 2004, Thomas F.O'Connell wrote: > >> Note: EXTRACT is not a true function. SQL defines it as an expression >> that happens to look similar to a function call. >> >> Also, are there other expressions that fall into this category? I >> don't >> know the spec well enough to know. > > At least COALESCE and NULLIF are not functions. > > Kris Jurka
"Thomas F.O'Connell" <tfo@sitening.com> writes:
> I'm thinking something like this (with thanks to Stephan):
> Note: EXTRACT is not a true function. SQL defines it as an expression
> that happens to look similar to a function call.
Rather than documenting this, maybe we should change the grammar to
allow it?
regards, tom lane
That seems reasonable, too, although I was interested to learn that this (and a few other expressions) weren't actually functions. Whether that's actually meaningful for any implementation purposes is debatable. Even if the grammar is changed to allow it, it's probably worth making a note of it in SQL compatibility documentation. Speaking of which, since functions aren't in the SQL Commands reference, where the compatibility documentation resides, does anyone see value in adding compatibility information to The SQL Language section as a whole? I can contribute what I know, but I don't have a full copy of the spec. -tfo On Sep 29, 2004, at 11:32 AM, Tom Lane wrote: > "Thomas F.O'Connell" <tfo@sitening.com> writes: >> I'm thinking something like this (with thanks to Stephan): > >> Note: EXTRACT is not a true function. SQL defines it as an expression >> that happens to look similar to a function call. > > Rather than documenting this, maybe we should change the grammar to > allow it? > > regards, tom lane
"Thomas F. O'Connell" <tfo@sitening.com> writes:
> That seems reasonable, too, although I was interested to learn that
> this (and a few other expressions) weren't actually functions.
They are functions ... but not from the point of view of the grammar,
which has special productions for them to cope with SQL's whimsical
syntax requirements.
regards, tom lane
Ah, so it's really a question of whether the syntactic sugar of CREATE INDEX is considered worthwhile by the developers (rather than a standards compliance issue) because CREATE INDEX is not a part of the SQL spec? Now that I understand what's going on, I don't have a strong preference, but I'd say that either it needs noting in the documentation or it should be added to the grammar. And if it isn't going to hit the grammar for 7.4.x, I'd be happy to supply a doc patch. -tfo On Sep 29, 2004, at 12:09 PM, Tom Lane wrote: > "Thomas F. O'Connell" <tfo@sitening.com> writes: >> That seems reasonable, too, although I was interested to learn that >> this (and a few other expressions) weren't actually functions. > > They are functions ... but not from the point of view of the grammar, > which has special productions for them to cope with SQL's whimsical > syntax requirements. > > regards, tom lane
"Thomas F. O'Connell" <tfo@sitening.com> writes:
> Ah, so it's really a question of whether the syntactic sugar of CREATE
> INDEX is considered worthwhile by the developers (rather than a
> standards compliance issue) because CREATE INDEX is not a part of the
> SQL spec?
Right. It is not a SQL-compliance item because CREATE INDEX isn't in
the standard in the first place.
The fact that the CREATE INDEX syntax allows for some things that look
like function calls but not for other things that look like function
calls is an annoyance, no doubt about it. I'm not sure how important
it is to fix though.
regards, tom lane
> The fact that the CREATE INDEX syntax allows for some things that look
> like function calls but not for other things that look like function
> calls is an annoyance, no doubt about it. I'm not sure how important
> it is to fix though.
Turns out to be easy to fix in the grammar, so I did it.
regards, tom lane
Nice. Thanks. My guess is that because this problem has existed until now there's no point in adding any notes to the 7.4.x docs? -tfo On Sep 29, 2004, at 7:46 PM, Tom Lane wrote: >> The fact that the CREATE INDEX syntax allows for some things that look >> like function calls but not for other things that look like function >> calls is an annoyance, no doubt about it. I'm not sure how important >> it is to fix though. > > Turns out to be easy to fix in the grammar, so I did it. > > regards, tom lane
On Wed, 29 Sep 2004, Tom Lane wrote: > "Thomas F.O'Connell" <tfo@sitening.com> writes: > > I'm thinking something like this (with thanks to Stephan): > > > Note: EXTRACT is not a true function. SQL defines it as an expression > > that happens to look similar to a function call. > > Rather than documenting this, maybe we should change the grammar to > allow it? That would work, but presumably then we should be doing all the function looking things, so probably CAST, EXTRACT, OVERLAY, POSITION, SUBSTRING, TREAT, TRIM, CONVERT (and any, if any, that weren't in the section of the grammar I saw those).