Help with 'now', now(), timestamp 'now', ...

>>>>> "edipoelder" == edipoelder  <edipoelder@ig.com.br> writes:
   edipoelder> times. Then I changed the function and run, at id =   edipoelder> 14. Change again at id = 15. Where is
underlined  edipoelder> (^^^^), i tried to put, 'now', timestamp 'now', etc,    edipoelder> and always get the same
time.What i'm doing wrong?    edipoelder> obs.: -> TABLE TEST (ID SERIAL, ANUMBER INTEGER)    -> TABLE TIMES (ID
SERIAL,START TIME, END TIME); PostgreSQL   -> 7.0.2 under Conectiva Linux
 

now() returns the time at the start of a transaction.  Since pl/psql
functions are implicit transactions, now() will not change inside a
function call.

I've used something like the following:

create function bm(integer) returns text as '
declare       cnt      alias for $1;       startt   text;       endt     text;
begin       startt := timeofday();       for i in 1..cnt LOOP        
           -- insert statement you want to time here
       end loop;       endt := timeofday();
       return delta_time_msecs(startt,endt);

end;' language 'plpgsql';

create function delta_time_msecs(text,text) returns float8 as '
declare       startt          alias for $1;       endt            alias for $2;       span            interval;
days           float8;       hours           float8;       minutes         float8;       mseconds        float8;
 
begin       span     := endt::timestamp - startt::timestamp;       mseconds :=
date_part(''milliseconds'',span)::float8;      minutes  := date_part(''minutes'',span)::float8;       hours    :=
date_part(''hours'',span)::float8;      days     := date_part(''days'',span)::float8;
 
       return abs(mseconds + minutes*60.0*1000.0 + hours*3600.0*1000.0 + days*24.0*3600.0*1000.0);

end;' language 'plpgsql';

select bm(1000)::float8/1000.0;

This will give you the average time, averaged over a thousand queries.  

-Dan