strange behaviour - Mailing list pgsql-admin

Hi to all,
I did a funciont for retrieve the actual time stamp
(not the time stamp of transaction):

CREATE FUNCTION sp_now (  )
RETURNS timestamp AS'
DECLARE
BEGIN
    RAISE NOTICE ''sp_now()'';
    RETURN timestamp(timeofday());
END;
' LANGUAGE 'plpgsql';


I notice that when I do some select like:

SELECT *
FROM user_traffic
WHERE start_date < sp_now();

the sp_now() is called for each row so I recreate
the function with the attribute iscachable:


CREATE FUNCTION sp_now (  )
RETURNS timestamp AS'
DECLARE

BEGIN
    RETURN timestamp(timeofday());
END;
' LANGUAGE 'plpgsql'
WITH (iscachable);

Now the select is working fine ( I mean only
one call for all records).

I create a function like this:

CREATE FUNCTION sp_foo (  )
RETURNS timestamp AS'
DECLARE
    my_time_stamp    timestamp;
BEGIN
    my_time_stamp := sp_now();

    RETURN my_time_stamp;
END;
' LANGUAGE 'plpgsql';


and with my surprise when I do a
series of this select :     select sp_now(), sp_foo();

I obtain:

empdb=# select sp_now(), sp_foo();
          sp_now           |          sp_foo
---------------------------+---------------------------
 2002-04-27 11:52:45.70+02 | 2002-04-27 11:52:45.70+02
(1 row)

empdb=# select sp_now(), sp_foo();
          sp_now           |          sp_foo
---------------------------+---------------------------
 2002-04-27 11:52:48.07+02 | 2002-04-27 11:52:45.70+02
(1 row)

empdb=# select sp_now(), sp_foo();
          sp_now           |          sp_foo
---------------------------+---------------------------
 2002-04-27 11:52:52.50+02 | 2002-04-27 11:52:45.70+02
(1 row)

empdb=# select sp_now(), sp_foo();
          sp_now           |          sp_foo
---------------------------+---------------------------
 2002-04-27 11:52:54.00+02 | 2002-04-27 11:52:45.70+02

It is normal that inside sp_foo() the sp_now() is not anymore called ?


Ciao
Gaetano

--
#exclude <windows>
#include <CSRSS>
printf("\t\t\b\b\b\b\b\b");.
printf("\t\t\b\b\b\b\b\b");