pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedINDEXng date_trunc ...
3+ messages / 3 participants
[nested] [flat]
* INDEXng date_trunc ...
@ 2001-11-22 14:41 Marc G. Fournier <scrappy@hub.org>
0 siblings, 1 reply; 3+ messages in thread
From: Marc G. Fournier @ 2001-11-22 14:41 UTC (permalink / raw)
To: pgsql-sql
In v7.2, how does one index a function?
CREATE INDEX hourly_stats_day
ON hourly_stats
USING btree ( DATE_TRUNC('day',runtime) ) ;
Gives me:
ERROR: parser: parse error at or near "'"
so I'm obviously doing it wrong :)
Thanks ...
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: INDEXng date_trunc ...
@ 2001-11-22 15:29 Brent Verner <brent@rcfile.org>
parent: Marc G. Fournier <scrappy@hub.org>
0 siblings, 1 reply; 3+ messages in thread
From: Brent Verner @ 2001-11-22 15:29 UTC (permalink / raw)
To: Marc G. Fournier <scrappy@hub.org>; +Cc: pgsql-sql
On 22 Nov 2001 at 09:41 (-0500), Marc G. Fournier wrote:
|
| In v7.2, how does one index a function?
|
| CREATE INDEX hourly_stats_day
| ON hourly_stats
| USING btree ( DATE_TRUNC('day',runtime) ) ;
|
| Gives me:
|
| ERROR: parser: parse error at or near "'"
from $htmldocs/indexes-functional.html:
"The function in the index definition can take more than one
argument, but they must be table columns, not constants.
Functional indexes are always single-column (namely, the
function result) even if the function uses more than one
input field; there cannot be multicolumn indexes that
contain function calls.
Tip: The restrictions mentioned in the previous paragraph
can easily be worked around by defining a custom
function to use in the index definition that computes
any desired result internally."
so...
create table test( id serial, tid timestamp default now() );
create function date_part_day(timestamp) returns float8 as '
select date_part(''day'',$1);
' language SQL with(iscachable);
create index ix_test_tid on test( date_part_day(tid) );
I have /no clue whatsoever/ how well that will work. Now, I'm curious
why we can't take a constant as an argument to an indexed function...
hth.
brent
--
"...curiousity and the cat."
Why do /I/ have to be that cat?
-- dbv
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: INDEXng date_trunc ...
@ 2001-11-22 17:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Brent Verner <brent@rcfile.org>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2001-11-22 17:29 UTC (permalink / raw)
To: Brent Verner <brent@rcfile.org>; +Cc: Marc G. Fournier <scrappy@hub.org>; pgsql-sql
Brent Verner <brent@rcfile.org> writes:
> ... I'm curious
> why we can't take a constant as an argument to an indexed function...
It's a representational limitation in pg_index: what's stored is a
function OID and a list of column numbers that are its arguments.
There's noplace to put any constant arguments.
I've been toying with the notion of replacing "functional indexes"
with "expressional indexes", wherein the value to be indexed is
computed by any SQL expression (probably refusing subselects and
aggregates though; the restrictions would be the same as for CHECK
constraint expressions). Other than breaking nigh everything that
looks at pg_index, this seems doable enough, and much more efficient
than writing SQL-language helper functions to accomplish the result.
(SQL-language functions work, but the call overhead is depressing.)
Haven't got round to it yet though ...
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2001-11-22 17:29 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-22 14:41 INDEXng date_trunc ... Marc G. Fournier <scrappy@hub.org>
2001-11-22 15:29 ` Brent Verner <brent@rcfile.org>
2001-11-22 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox