Subject: Indexes on Expressions -- Parentheses

From: Thomas F.O'Connell <[email-masked]>
Date: Tue, 28 Sep 2004 23:44:28 -0400

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

I get a syntax error corresponding to the open parenthesis after 
extract (and whitespace is not the issue).

If I add an enclosing set of parentheses, E.g.:

db=# CREATE INDEX expression_idx on some_table( ( extract( year from 
some_column ) ) );

it works!

Is this a known issue?

-tfo

---

From: Stephan Szabo <[email-masked]>
Date: Tue, 28 Sep 2004 22:30:55 -0700 (PDT)

[email-masked]

> db=# CREATE INDEX expression_idx on some_table( extract( year from

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.

---

From: Thomas F.O'Connell <[email-masked]>
Date: Wed, 29 Sep 2004 08:35:32 -0400

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

---

From: Kris Jurka <[email-masked]>
Date: Wed, 29 Sep 2004 08:25:59 -0500 (EST)

> know the spec well enough to know.

At least COALESCE and NULLIF are not functions.

Kris Jurka

---

From: Thomas F.O'Connell <[email-masked]>
Date: Wed, 29 Sep 2004 09:34:11 -0400

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 29 Sep 2004 11:32:24 -0400

> 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

---

From: Stephan Szabo <[email-masked]>
Date: Wed, 29 Sep 2004 08:40:10 -0700 (PDT)

> Rather than documenting this, maybe we should change the grammar to

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

---

From: Thomas F.O'Connell <[email-masked]>
Date: Wed, 29 Sep 2004 11:42:26 -0400

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 29 Sep 2004 12:09:02 -0400

"Thomas F. O'Connell" <[email-masked]> writes:
> 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

---

From: Thomas F.O'Connell <[email-masked]>
Date: Wed, 29 Sep 2004 18:46:39 -0400

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 29 Sep 2004 18:49:51 -0400

> standards compliance issue) because CREATE INDEX is not a part of the 

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 29 Sep 2004 19:46:04 -0400

> calls is an annoyance, no doubt about it.  I'm not sure how important

Turns out to be easy to fix in the grammar, so I did it.

			regards, tom lane

---

From: Thomas F.O'Connell <[email-masked]>
Date: Wed, 29 Sep 2004 22:33:51 -0400

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