Received: from zukerman-1.dsl.speakeasy.net (zukerman-1.dsl.speakeasy.net [216.254.75.142]) by hub.org (8.10.1/8.10.1) with ESMTP id e6VGKxB01095 for ; Mon, 31 Jul 2000 12:21:00 -0400 (EDT) Received: from (matt.w80.math-hat.com) [216.254.75.141] by zukerman-1.dsl.speakeasy.net with esmtp (Exim 3.12 #1 (Debian)) id 13JI5n-0003Co-00; Mon, 31 Jul 2000 12:07:03 -0400 Received: from zukerman by matt.w80.math-hat.com with local (Exim 3.12 #1 (Debian)) id 13JI6Q-0001JW-00; Mon, 31 Jul 2000 12:07:42 -0400 To: Tom Lane Cc: pgsql-sql@postgresql.org Subject: Re: Indices and time spans References: <87g0oqbej1.fsf@matt.w80.math-hat.com> <9449.965054960@sss.pgh.pa.us> From: Itai Zukerman Date: 31 Jul 2000 12:07:42 -0400 In-Reply-To: Tom Lane's message of "Mon, 31 Jul 2000 10:49:20 -0400" Message-ID: <878zui1dwx.fsf@matt.w80.math-hat.com> Lines: 24 User-Agent: Gnus/5.0807 (Gnus v5.8.7) Emacs/20.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200007/287 > You need to recast the clause as something like > > WHERE posted >= (CURRENT_TIMESTAMP - '5 minutes'::TIMESPAN) > > Then you still have the problem of persuading Postgres that it should > treat the right side of this as a constant and not something to > re-evaluate at each row. In 7.0 and later it's possible to do that > with creative use of a user-defined function marked "iscachable" > (for details see the archives for the last time this question came up, > a few months back). That did the trick, thanks! Just for reference: CREATE FUNCTION time_machine ( TIMESPAN ) RETURNS TIMESTAMP AS 'SELECT CURRENT_TIMESTAMP - $1' LANGUAGE 'sql' WITH (iscachable); seems to work. Perhaps it's more complicated than that, though? -itai