Thread: INDEXng date_trunc ...

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

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

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