Subject | Making evaluation of UDFs constant across a query |
---|---|
Author | Mike Pomraning |
Post date | 2011-03-18T15:41:10Z |
Is there a technique to make the return value of a UDF constant across
the execution of a single query?
For example, I'd like getExactTimestamp() -- or, rather, a UDF very
much like it -- to return the same result every time it is evaluated,
much like the context variable CURRENT_TIMESTAMP does. Right now, of
course, the UDF is evaluated over and over, returning a different
result each time. This effect can be seen if we introduce a function
(FB_SLEEP()) to pause execution for a second or so with every row in
the result set:
SQL> select count(1) from threecount;
COUNT
============
3
SQL> select current_timestamp, getexacttimestamp(), cast('now' as
timestamp) AS NOW, fb_sleep(1) from threecount;
CURRENT_TIMESTAMP GETEXACTTIMESTAMP
NOW FB_SLEEP
========================= =========================
========================= ============
2011-03-17 17:52:21.5510 2011-03-17 17:52:22.5511 2011-03-17
17:52:22.5510 0
2011-03-17 17:52:21.5510 2011-03-17 17:52:23.5511 2011-03-17
17:52:23.5510 0
2011-03-17 17:52:21.5510 2011-03-17 17:52:24.5511 2011-03-17
17:52:24.5510 0
So, can I make an arbitrary UDF instead behave like CURRENT_TIMESTAMP?
I didn't see a feature request to this effect on the FB tracker, so I
thought I'd ask here. Thanks.
-Mike
the execution of a single query?
For example, I'd like getExactTimestamp() -- or, rather, a UDF very
much like it -- to return the same result every time it is evaluated,
much like the context variable CURRENT_TIMESTAMP does. Right now, of
course, the UDF is evaluated over and over, returning a different
result each time. This effect can be seen if we introduce a function
(FB_SLEEP()) to pause execution for a second or so with every row in
the result set:
SQL> select count(1) from threecount;
COUNT
============
3
SQL> select current_timestamp, getexacttimestamp(), cast('now' as
timestamp) AS NOW, fb_sleep(1) from threecount;
CURRENT_TIMESTAMP GETEXACTTIMESTAMP
NOW FB_SLEEP
========================= =========================
========================= ============
2011-03-17 17:52:21.5510 2011-03-17 17:52:22.5511 2011-03-17
17:52:22.5510 0
2011-03-17 17:52:21.5510 2011-03-17 17:52:23.5511 2011-03-17
17:52:23.5510 0
2011-03-17 17:52:21.5510 2011-03-17 17:52:24.5511 2011-03-17
17:52:24.5510 0
So, can I make an arbitrary UDF instead behave like CURRENT_TIMESTAMP?
I didn't see a feature request to this effect on the FB tracker, so I
thought I'd ask here. Thanks.
-Mike