Message-ID: <4162BFCF.9090501@informatik.hu-berlin.de>
Date: Tue, 05 Oct 2004 17:37:51 +0200
From: Silke Trissl <trissl@informatik.hu-berlin.de>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; de-AT; rv:1.4) Gecko/20030624
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Using timestamp in function
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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 IN 1..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