Skip site navigation (1) Skip section navigation (2)

strange behaviour

From: "Gaetano Mendola" <mendola(at)bigfoot(dot)com>
To: <pgsql-admin(at)postgresql(dot)org>
Subject: strange behaviour
Date: 2002-04-27 09:55:55
Message-ID: 00c301c1edd1$b9cc5b50$d5a9fea9@GMENDOLA2 (view raw or flat)
Thread:
Lists: 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");


Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2002-04-27 15:17:19
Subject: Re: strange behaviour
Previous:From: Bruce MomjianDate: 2002-04-26 23:33:57
Subject: Re: smart copy?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group