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

>>>>> "edipoelder" == edipoelder <edipoelder(at)ig(dot)com(dot)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