public inbox for [email protected]
help / color / mirror / Atom feedIndexes on Expressions -- Parentheses
13+ messages / 4 participants
[nested] [flat]
* Indexes on Expressions -- Parentheses
@ 2004-09-29 03:44 Thomas F.O'Connell <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Thomas F.O'Connell @ 2004-09-29 03:44 UTC (permalink / raw)
To: General <[email protected]>
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Indexes on Expressions -- Parentheses
@ 2004-09-29 05:30 Stephan Szabo <[email protected]>
parent: Thomas F.O'Connell <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Stephan Szabo @ 2004-09-29 05:30 UTC (permalink / raw)
To: Thomas F.O'Connell <[email protected]>; +Cc: General <[email protected]>
[email protected]
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* EXTRACT Clarification
@ 2004-09-29 12:35 Thomas F.O'Connell <[email protected]>
parent: Stephan Szabo <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Thomas F.O'Connell @ 2004-09-29 12:35 UTC (permalink / raw)
To: Stephan Szabo <[email protected]>; +Cc: pgsql-docs
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:
> [email protected]
>
> 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-29 13:25 Kris Jurka <[email protected]>
parent: Thomas F.O'Connell <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Kris Jurka @ 2004-09-29 13:25 UTC (permalink / raw)
To: Thomas F.O'Connell <[email protected]>; +Cc: Stephan Szabo <[email protected]>; pgsql-docs
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-29 13:34 Thomas F.O'Connell <[email protected]>
parent: Kris Jurka <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Thomas F.O'Connell @ 2004-09-29 13:34 UTC (permalink / raw)
To: Kris Jurka <[email protected]>; +Cc: Stephan Szabo <[email protected]>; pgsql-docs
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-29 15:32 Tom Lane <[email protected]>
parent: Thomas F.O'Connell <[email protected]>
1 sibling, 2 replies; 13+ messages in thread
From: Tom Lane @ 2004-09-29 15:32 UTC (permalink / raw)
To: Thomas F.O'Connell <[email protected]>; +Cc: Stephan Szabo <[email protected]>; pgsql-docs
"Thomas F.O'Connell" <[email protected]> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-29 15:40 Stephan Szabo <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Stephan Szabo @ 2004-09-29 15:40 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Thomas F.O'Connell <[email protected]>; pgsql-docs
On Wed, 29 Sep 2004, Tom Lane wrote:
> "Thomas F.O'Connell" <[email protected]> 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).
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-29 15:42 Thomas F.O'Connell <[email protected]>
parent: Tom Lane <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Thomas F.O'Connell @ 2004-09-29 15:42 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Stephan Szabo <[email protected]>; pgsql-docs
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" <[email protected]> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-29 16:09 Tom Lane <[email protected]>
parent: Thomas F.O'Connell <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2004-09-29 16:09 UTC (permalink / raw)
To: Thomas F. O'Connell <[email protected]>; +Cc: Stephan Szabo <[email protected]>; pgsql-docs
"Thomas F. O'Connell" <[email protected]> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-29 22:46 Thomas F.O'Connell <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Thomas F.O'Connell @ 2004-09-29 22:46 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Stephan Szabo <[email protected]>; pgsql-docs
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" <[email protected]> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-29 22:49 Tom Lane <[email protected]>
parent: Thomas F.O'Connell <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2004-09-29 22:49 UTC (permalink / raw)
To: Thomas F. O'Connell <[email protected]>; +Cc: Stephan Szabo <[email protected]>; pgsql-docs
"Thomas F. O'Connell" <[email protected]> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-29 23:46 Tom Lane <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2004-09-29 23:46 UTC (permalink / raw)
To: Thomas F. O'Connell <[email protected]>; Stephan Szabo <[email protected]>; pgsql-docs
> 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: EXTRACT Clarification
@ 2004-09-30 02:33 Thomas F.O'Connell <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Thomas F.O'Connell @ 2004-09-30 02:33 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Stephan Szabo <[email protected]>; pgsql-docs
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
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2004-09-30 02:33 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-09-29 03:44 Indexes on Expressions -- Parentheses Thomas F.O'Connell <[email protected]>
2004-09-29 05:30 ` Stephan Szabo <[email protected]>
2004-09-29 12:35 ` EXTRACT Clarification Thomas F.O'Connell <[email protected]>
2004-09-29 13:25 ` Re: EXTRACT Clarification Kris Jurka <[email protected]>
2004-09-29 13:34 ` Re: EXTRACT Clarification Thomas F.O'Connell <[email protected]>
2004-09-29 15:32 ` Re: EXTRACT Clarification Tom Lane <[email protected]>
2004-09-29 15:40 ` Re: EXTRACT Clarification Stephan Szabo <[email protected]>
2004-09-29 15:42 ` Re: EXTRACT Clarification Thomas F.O'Connell <[email protected]>
2004-09-29 16:09 ` Re: EXTRACT Clarification Tom Lane <[email protected]>
2004-09-29 22:46 ` Re: EXTRACT Clarification Thomas F.O'Connell <[email protected]>
2004-09-29 22:49 ` Re: EXTRACT Clarification Tom Lane <[email protected]>
2004-09-29 23:46 ` Re: EXTRACT Clarification Tom Lane <[email protected]>
2004-09-30 02:33 ` Re: EXTRACT Clarification Thomas F.O'Connell <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox