Thread: Using a User-Def function in a query
I've written several user-defined functions (UDFs) for converting dates to unix time, every which way.
They work find, ala
# select dtu_dmony('22 Sep 2008');
dtu_dmony
------------
1222066800
(1 row)
Returns an integer.
---
Here's a typical query I often run (why I wrote the functions
# select count(distinct username) from stats where eventtime > 1222066800 ;
count
-------
1460
(1 row)
---
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...
---
Is it the table-like formatting that's killing me?
How do I get around this?
Thanks!
Ralph Smith
smithrn at here washington.edu
=====================
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
Ralph Smith <smithrn@washington.edu> writes:I've written several user-defined functions (UDFs) for convertingdates 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, t lane
There is no index on that table, partially because I always have to do a serial scan on it and an index isn't used. It's a huge table.
# \d stats+
Table "public.stats"
Column | Type | Modifiers
-----------+-----------------------+-----------
lab | character varying(30) |
name | character varying(50) |
status | character varying(40) |
eventtime | integer |
username | character varying(30) |
pkey | character varying(60) |
# EXPLAIN select count(*) from stats where eventtime > 1221894000 ;
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=269556.18..269556.19 rows=1 width=0)
-> Seq Scan on stats (cost=0.00..269466.96 rows=35687 width=0)
Filter: (eventtime > 1221894000)
(3 rows)
I made all my functions STABLE, but no change.
It takes about 22 seconds to run the function-less query above.
Do you think I need to use IMMUTABLE?
I'll give it a go in the meantime.
Thanks again all,
Ralph
=========================
On Sep 23, 2008, at 7:50 PM, Tom Lane wrote:
> 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, t lane
========================================
IMMUTABLE Worked!
Thank you!
Ralph