Re: [SQL] Problem with timestamp

[Henshall, Stuart - Design & Print]

Title: RE: [SQL] Problem with timestamp

Beatrice Yueksel wrote:
> 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 the interval.
> 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 into t2 now();
>     t3 := t2 - t1;
>     RAISE NOTICE '' from % to % = %'',t2,t1,t3;
>     return t3;
>
> end;
> ' language 'plpgsql';
>
use timeofday(); as the others have the same value throughout the transaction
- Stuart