Indexes on Expressions -- Parentheses

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.

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:

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:

"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

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).

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:

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:

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: