Re: Fwd: Approximate join on timestamps

"Rhys Stewart" <rhys(dot)stewart(at)gmail(dot)com> writes:

> had a similar problem a while back. so i made and abs_time function:
>
> CREATE OR REPLACE FUNCTION abs_time(interval)
> RETURNS interval AS
> $BODY$
> BEGIN
> if
> $1 < '00:00:00'::interval
> then
> return ($1 * -1)::interval;
> else
> return $1;
> END IF;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;

I believe that you can declare this IMMUTABLE. For a given interval it will
always return the same value, so you can benefit from some optimization.

http://www.postgresql.org/docs/8.2/interactive/xfunc-volatility.html

An IMMUTABLE function cannot modify the database and is guaranteed to
return the same results given the same arguments forever. This
category allows the optimizer to pre-evaluate the function when a
query calls it with constant arguments. For example, a query like
SELECT ... WHERE x = 2 + 2 can be simplified on sight to SELECT
... WHERE x = 4, because the function underlying the integer addition
operator is marked IMMUTABLE.

--
Jorge Godoy <jgodoy(at)gmail(dot)com>