Firebird constant UDF

Is it possible to make an EXTERNAL FUNCTION as constant or immutable, so that Firebird knows not to recompute it during the course of one SQL statement?

In the example below (Firebird 2.1), I'd like GETTIMEOFDAY() to behave like CURRENT_TIMESTAMP, but it is instead evaluated twice:

SQL> DECLARE EXTERNAL FUNCTION gettimeofday -- gettimeofday(2) wrapper
CON>         RETURNS DOUBLE PRECISION BY VALUE
CON>         ENTRY_POINT 'UDF_gettimeofday' MODULE_NAME 'udf_gettimeofday';

SQL> SELECT CURRENT_TIMESTAMP AS ts,
CON>        CAST(GETTIMEOFDAY() AS INTEGER) AS time_t,
CON>        FB_SLEEP(2) AS zzz
CON>   FROM rdb$database
CON>  CROSS JOIN (SELECT 1 AS foo
CON>                FROM rdb$database
CON>               UNION ALL
CON>              SELECT 2
CON>                FROM rdb$database) d;

                       TS       TIME_T          ZZZ
========================= ============ =开发者_如何学Python===========
2011-03-15 20:57:46.0390    1300244268            0
2011-03-15 20:57:46.0390    1300244270            0

As you can see, the value of "TS" remains constant, but my "TIME_T" advances across the FB_SLEEP() calls. (FB_SLEEP is a convenience function to pause for the given number of seconds.)

Is what I want possible? I know PostgreSQL permits precisely this with its concept of STABLE FUNCTIONS.

---

AFAIK, you cannot mark a UDF as constant or inmutable in Firebird, but as a workaround you can rely on a in-line view (aka derived table) to achieve what you want: select the value just one time and use it as a constant in your results. I do not have any UDF at hand to make a test, so maybe some syntax error, but I hope you catch the idea behind this:

SELECT CURRENT_TIMESTAMP AS ts,
        q1.time_t,
        FB_SLEEP(2) AS zzz
   FROM rdb$database
  CROSS JOIN (select CAST(GETTIMEOFDAY() AS INTEGER) AS time_t from rdb$database)
  CROSS JOIN (SELECT 1 AS foo
                FROM rdb$database
               UNION ALL
              SELECT 2
                FROM rdb$database) d;

You can also rely on a selectable stored procedure to run the udf once and add the column to the results of a given query

Edit As requested, I'm including the Stored procedure:

SET TERM ^ ;

CREATE PROCEDURE ExampleSP 
RETURNS 
(
  ts timestamp
, time_t integer
, zzz integer
)
as
BEGIN
  SELECT CAST(GetTimeOfDay() AS Integer)
    FROM rdb$database
    INTO :time_t;
  for SELECT Current_Timestamp AS ts,
             FB_SLEEP(2) AS zzz
        FROM rdb$database
       CROSS JOIN (SELECT 1 AS foo
                     FROM rdb$database
                    UNION ALL
                   SELECT 2
                     FROM rdb$database) d
        INTO :ts, :zzz do
    SUSPEND;
END
^

SET TERM ; ^

SELECT * FROM ExampleSP;

---

In Brief

The short answer is "no," and the accompanying guidance is "always run your server in UTC."

Workarounds

• Simplest case: stable UTC timestamps
  (This was my original aim.) If CURRENT_TIMESTAMP is precise enough, simply run your server in UTC. No UDF required.

• Explicitly precompute UDF
  There's no directly supported way to "stabilize" a UDF. So, most clients are better off simply pre-computing the UDF's return value, making that literal value available as a client-supplied parameter, in a GTT, etc.

Kludge

CURRENT_TRANSACTION and CURRENT_TIMESTAMP taken together effectively identify an individual query, at least to the precision of CURRENT_TIMESTAMP. (Again presuming that the clock is in UTC, lest time repeat itself during a daylight savings change.)

That in mind, a selectable stored procedure could cache the UDF's return value as a string using RDB$SET_CONTEXT and RDB$GET_CONTEXT, storing in the USER_TRANSACTION context and keying off of CURRENT_TIMESTAMP. Add in a little extra logic to prune the number of entries stored under USER_TRANSACTION, too. Yuck.