Ok. Right after I posted I noticed this:
"It is important to know that CURRENT_TIMESTAMP and related functions return
the start time of the current transaction; their values do not change during
the transaction. This is considered a feature: the intent is to allow a
single transaction to have a consistent notion of the "current" time, so
that multiple modifications within the same transaction bear the same time
stamp."
Using timeofday(), gives me the result I want (timeofday()::timestamp casts
it nicely to a timestamp).
Yosef Haas
Lead Developer
KarateDepot.com
845-875-6423
yosef@karatedepot.com
_____
From: pgsql-bugs-owner@postgresql.org
[mailto:pgsql-bugs-owner@postgresql.org] On Behalf Of Yosef Haas
Sent: Tuesday, January 30, 2007 9:44 AM
To: pgsql-bugs@postgresql.org
Subject: [BUGS] now() in PL/pgSQL Functions
This is in version 8.1.4.
I've noticed what seems to be a strange behavior - it may be by design, but
I figured I'd ask.
Run this simple test function:
create or replace function test() RETURNS bool AS '
begin
raise notice ''%'',now();
for i IN 0..50000000 loop
end loop;
raise notice ''%'',now();
return true;
end;
'
LANGUAGE 'plpgsql';
It should print the current date, wait a few seconds (by counting to 50
million)
And then print the current date. Clearly, the two dates are not identical;
however this is how it executes:
catalog=# select test();
NOTICE: 2007-01-30 09:33:19.323702-05
NOTICE: 2007-01-30 09:33:19.323702-05
test
------
t
(1 row)
For some reason it is using the same value for both "now()" calls. Is this a
bug, or by design? If it's by design what can I do to get the right time. I
know that the function only returns when it's finished executing, but
shouldn't now() return the actual time and not the time that the function
begins?
Thanks,
Yosef Haas
yosef@karatedepot.com