Thread: Using timestamp in function

Hi,

I am using PostgreSQL 7.4 and was trying to log the time each part of a 
function needs. I found a pretty helpful bit of code in the documentation:

http://www.postgresql.org/docs/7.4/static/plpgsql-expressions.html

I used the following function, called inside the another function:

CREATE or replace FUNCTION mylog_test(integer, varchar) RETURNS 
timestamp AS '    DECLARE        n ALIAS FOR $1;        logtxt ALIAS FOR $2;        curtime timestamp;    BEGIN
curtime:= ''now'';        --INSERT INTO logger VALUES ( nextval(''seq_log''), curtime, 
 
substr(logtxt,0,200));        RAISE NOTICE ''TIME: %'',curtime;        RETURN curtime;    END;
' LANGUAGE plpgsql;

I expected, that the variable curtime gets a new time value, each time 
the function is called (at least that is what I understood from the 
documentation). This works fine, if I test it with
SELECT mylog_test(5, 'test');
But as soon as I call the funtion from another function (which I need) 
the variable curtime does not change anymore.

Can anyone tell me why this does not work and does anyone know a 
solution to this?

For test purposes here is a function called test, which does nothing 
else than to call mylog_test(..) and spend some time calculating.

CREATE or replace FUNCTION test() RETURNS text AS '  DECLARE    i integer;    j integer;    k integer;  BEGIN    FOR i
IN1..10 LOOP      PERFORM mylog(3, ''val '' || i);      FOR j IN 1..2000000 LOOP        k:=j;      END LOOP;    END
LOOP;   RETURN ''THE END'';  END;
 
' LANGUAGE 'plpgsql';


SELECT test();

Any help is appreciated

Silke

Silke Trissl <trissl@informatik.hu-berlin.de> writes:
> I expected, that the variable curtime gets a new time value, each time 
> the function is called (at least that is what I understood from the 
> documentation). This works fine, if I test it with
> SELECT mylog_test(5, 'test');
> But as soon as I call the funtion from another function (which I need) 
> the variable curtime does not change anymore.

"now" refers to the transaction start time.  You can get at current time
of day with the timeofday() function.
        regards, tom lane

On Tue, Oct 05, 2004 at 05:37:51PM +0200, Silke Trissl wrote:

> CREATE or replace FUNCTION mylog_test(integer, varchar) RETURNS 
> timestamp AS '
>     DECLARE
>         n ALIAS FOR $1;
>         logtxt ALIAS FOR $2;
>         curtime timestamp;
>     BEGIN
>         curtime := ''now'';
>         --INSERT INTO logger VALUES ( nextval(''seq_log''), curtime, 
> substr(logtxt,0,200));
>         RAISE NOTICE ''TIME: %'',curtime;
>         RETURN curtime;
>     END;
> ' LANGUAGE plpgsql;
> 
> I expected, that the variable curtime gets a new time value, each time 
> the function is called (at least that is what I understood from the 
> documentation). This works fine, if I test it with
> SELECT mylog_test(5, 'test');
> But as soon as I call the funtion from another function (which I need) 
> the variable curtime does not change anymore.

"Functions and trigger procedures are always executed within a
transaction established by an outer query...." [1]

"It is important to know that CURRENT_TIMESTAMP and related functions
return the start time of the current transaction; their values do not
change during the transaction....timeofday() returns the wall-clock
time and does advance during transactions." [2]

[1] http://www.postgresql.org/docs/7.4/static/plpgsql-structure.html
[2] http://www.postgresql.org/docs/7.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/