pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Two TIMESTAMPs in one pl/sql function 2+ messages / 2 participants [nested] [flat]
* Two TIMESTAMPs in one pl/sql function @ 2002-11-26 13:54 Rison, Stuart <srison@rvc.ac.uk> 0 siblings, 1 reply; 2+ messages in thread From: Rison, Stuart @ 2002-11-26 13:54 UTC (permalink / raw) To: pgsql-sql Hi, I'm trying to time a pl/sql function using a rougn and ready method, basically: print a TIMESTAMP at the begining of the function, print a TIMESTAMP at the end of the function. So...: CREATE OR REPLACE FUNCTION timer() RETURNS INTEGER AS ' DECLARE timer1 TIMESTAMP; timer2 TIMESTAMP; num_operators INTEGER; BEGIN timer1 := ''now''; -- As suggested in 23.4 of programmer guide RAISE NOTICE ''Start: %'', timer1; /* Some function which takes time. Here, a select from a pg catalogue */ SELECT INTO num_operators COUNT(*) FROM pg_operator; timer2 := ''now''; RAISE NOTICE ''End: %'', timer2; RETURN(num_operators); END;' LANGUAGE 'plpgsql'; Gives me: testdb2=# select timer(); NOTICE: Start: 2002-11-26 13:40:14.116605+00 NOTICE: End: 2002-11-26 13:40:14.116605+00 timer ------- 623 (1 row) I've tried all sorts of variations but I hit one of two problems; either the TIMESTAMP is fixed to the function compile time or, as above, the timer1 and timer2 TIMESTAMPs are always identical. Any help/pointers/suggestions appreciate... well of course a working solution would be the best ;) Cheers, Stuart. ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Two TIMESTAMPs in one pl/sql function @ 2002-11-26 15:24 Richard Huxton <dev@archonet.com> parent: Rison, Stuart <srison@rvc.ac.uk> 0 siblings, 0 replies; 2+ messages in thread From: Richard Huxton @ 2002-11-26 15:24 UTC (permalink / raw) To: Rison, Stuart <srison@rvc.ac.uk>; pgsql-sql On Tuesday 26 Nov 2002 1:54 pm, Rison, Stuart wrote: > Hi, > > I'm trying to time a pl/sql function using a rougn and ready method, > basically: print a TIMESTAMP at the begining of the function, print a > TIMESTAMP at the end of the function. [snip] > Gives me: > > testdb2=# select timer(); > NOTICE: Start: 2002-11-26 13:40:14.116605+00 > NOTICE: End: 2002-11-26 13:40:14.116605+00 > I've tried all sorts of variations but I hit one of two problems; either > the TIMESTAMP is fixed to the function compile time or, as above, the > timer1 and timer2 TIMESTAMPs are always identical. Try timeofday() not now(). Quite often you want the time to stay fixed for the length of a transaction (what now() does). In this case you don't - see the Functions : date/time section of the manual for details. -- Richard Huxton ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-11-26 15:24 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2002-11-26 13:54 Two TIMESTAMPs in one pl/sql function Rison, Stuart <srison@rvc.ac.uk> 2002-11-26 15:24 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox