pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
INDEXng date_trunc ...
3+ messages / 3 participants
[nested] [flat]

* INDEXng date_trunc ...
@ 2001-11-22 14:41 Marc G. Fournier <scrappy@hub.org>
  2001-11-22 15:29 ` Re: INDEXng date_trunc ... Brent Verner <brent@rcfile.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 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   ` Re: INDEXng date_trunc ... Tom Lane <tgl@sss.pgh.pa.us>
  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 14:41 INDEXng date_trunc ... Marc G. Fournier <scrappy@hub.org>
  2001-11-22 15:29 ` Re: INDEXng date_trunc ... Brent Verner <brent@rcfile.org>
@ 2001-11-22 17:29   ` Tom Lane <tgl@sss.pgh.pa.us>
  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