Re: Using a User-Def function in a query - Mailing list pgsql-general

Ralph Smith <smithrn@washington.edu> writes:
> I've written several user-defined functions (UDFs) for converting
> dates to unix time, every which way.
> ... but when I try to use the function in a query
>      # select count(distinct username) from stats where eventtime >
> dtu_dmony('22 Sep 2008') ;
> it never comes back...

Did you EXPLAIN that query?  Is it using the index I suppose you've got
on eventtime?  I'll bet that it's not, and that the reason why not is
that you didn't mark the function IMMUTABLE (or STABLE, which is the
correct marking if it depends on the timezone setting).  The planner
won't try to use volatile functions in index conditions.

            regards, tom lane