Thread: Problem with timestamp

Hello,

I try to check the time needed by a function.
I would like to :. select current_timestamp,. execute 5000 times a function. select the current_timestamp and return
theinterval.
 
Problem : the interval is always '00:00', the two timestamps have always 
the same value.
Could you help me ? have you any suggestions for testing sql functions 
speed?
Thank you in advance,
Béatrice


create function test_function() returns interval
as '
declare   j integer;   t1 timestamp;   t2 timestamp;   t3 interval;   x integer;
begin   select into t1 now();   FOR j in 0..5000 loop       select into x get_function_to_test();   end loop;   select
intot2 now();   t3 := t2 - t1;   RAISE NOTICE '' from % to % = %'',t2,t1,t3;   return t3;
 

end;
' language 'plpgsql';

<p><font size="2">Beatrice Yueksel wrote:</font><br /><font size="2">> Hello,</font><br /><font size="2">>
</font><br/><font size="2">> I try to check the time needed by a function.</font><br /><font size="2">> I would
liketo :</font><br /><font size="2">>  . select current_timestamp,</font><br /><font size="2">>  . execute 5000
timesa function</font><br /><font size="2">>  . select the current_timestamp and return the interval.</font><br
/><fontsize="2">> Problem : the interval is always '00:00', the two timestamps have</font><br /><font size="2">>
alwaysthe same value.</font><br /><font size="2">> Could you help me ? have you any suggestions for testing sql
functions</font><br/><font size="2">> speed?</font><br /><font size="2">> Thank you in advance,</font><br /><font
size="2">>Béatrice</font><br /><font size="2">> </font><br /><font size="2">> </font><br /><font size="2">>
createfunction test_function() returns interval</font><br /><font size="2">> as '</font><br /><font size="2">>
declare</font><br/><font size="2">>     j integer;</font><br /><font size="2">>     t1 timestamp;</font><br
/><fontsize="2">>     t2 timestamp;</font><br /><font size="2">>     t3 interval;</font><br /><font
size="2">>    x integer;</font><br /><font size="2">> begin</font><br /><font size="2">>     select into t1
now();</font><br/><font size="2">>     FOR j in 0..5000 loop</font><br /><font size="2">>         select into x
get_function_to_test();</font><br/><font size="2">>     end loop;</font><br /><font size="2">>     select into t2
now();</font><br/><font size="2">>     t3 := t2 - t1;</font><br /><font size="2">>     RAISE NOTICE '' from % to
%= %'',t2,t1,t3;</font><br /><font size="2">>     return t3;</font><br /><font size="2">> </font><br /><font
size="2">>end;</font><br /><font size="2">> ' language 'plpgsql';</font><br /><font size="2">> </font><br
/><fontsize="2">use timeofday(); as the others have the same value throughout the transaction</font><br /><font
size="2">-Stuart</font>