Received: from habanero.hesketh.net (habanero.hesketh.net [66.45.6.196]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAMFSxm40415 for ; Thu, 22 Nov 2001 10:28:59 -0500 (EST) (envelope-from brent@mutt.rcfile.org) Received: from mutt.rcfile.org (rdu57-26-120.nc.rr.com [66.57.26.120]) by habanero.hesketh.net (8.11.6/8.11.1) with ESMTP id fAMFT0U07406; Thu, 22 Nov 2001 10:29:00 -0500 X-Received-From: brent@mutt.rcfile.org X-Delivered-To: pgsql-sql@postgresql.org X-Spam-Filter: check_local@habanero.hesketh.net by digitalanswers.org X-More-Information: http://spamfighter.hesketh.net Received: (from brent@localhost) by mutt.rcfile.org (8.11.6/8.11.5) id fAMFT5E34344; Thu, 22 Nov 2001 10:29:05 -0500 (EST) (envelope-from brent) Date: Thu, 22 Nov 2001 10:29:05 -0500 From: Brent Verner To: "Marc G. Fournier" Cc: pgsql-sql@postgresql.org Subject: Re: INDEXng date_trunc ... Message-ID: <20011122102905.A34094@rcfile.org> References: <20011122094048.T64011-100000@earth.hub.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20011122094048.T64011-100000@earth.hub.org> User-Agent: Mutt/1.3.23i X-Archive-Number: 200111/319 X-Sequence-Number: 5701 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