pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Brent Verner <brent@rcfile.org>
To: Marc G. Fournier <scrappy@hub.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: INDEXng date_trunc ...
Date: Thu, 22 Nov 2001 10:29:05 -0500
Message-ID: <20011122102905.A34094@rcfile.org> (raw)
In-Reply-To: <20011122094048.T64011-100000@earth.hub.org>
References: <20011122094048.T64011-100000@earth.hub.org>

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



view thread (3+ messages)  latest in thread

Message-ID: <20011122102905.A34094@rcfile.org>
Permalink:  ../20011122102905.A34094@rcfile.org/
Also on:    postgresql.org/message-id/20011122102905.A34094@rcfile.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: brent@rcfile.org, scrappy@hub.org
  Subject: Re: INDEXng date_trunc ...
  In-Reply-To: <20011122102905.A34094@rcfile.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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