Two TIMESTAMPs in one pl/sql function

From: "Rison, Stuart" <srison(at)rvc(dot)ac(dot)uk>
To: "'pgsql-sql(at)postgresql(dot)org'" <pgsql-sql(at)postgresql(dot)org>
Subject: Two TIMESTAMPs in one pl/sql function
Date: 2002-11-26 13:54:20
Message-ID: 6BD8CE460CC6EE40B83DDFCED609F84BE140D9@cmnt4008.rvc.ac.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Henshall, Stuart - Design & Print 2002-11-26 15:12:02 Re: How does postgres handle non literal string values
Previous Message Rison, Stuart 2002-11-26 13:54:03 [SQL/BUGS] (possible) inconsistent behaviour of timestamp?