Re: Fwd: Approximate join on timestamps

"Rhys Stewart" <rhys.stewart@gmail.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@gmail.com>