I am starting to see Tom's issue here. If you have a PL/pgSQL function
that does:
> >>DECLARE
> >>BEGIN
> >> SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL;
> >> SELECT CURRENT_TIMESTAMP INTO time2 FROM DUAL;
> >>END;
You would want those two to be the same because they are in the same
function, but by looking at it, they look the same as interactive
queries. In a sense if we change CURRENT_TIMESTAMP, we are scoping the
variable to match the users/client's perspective.
However, we have added statement_timeout, so it does seem we have had to
move to a more user-centered perspective on some of these things. The
big question is whether a variable that would be inserted into the
database should have such scoping. I can see cases where people would
want that, and others where they wouldn't.
> 1. RDBMS start: No one
> 2. Session start: No one
> 3. Transaction start: PostgreSQL
> 4. Statement start: ???
> 5. CURRENT_TIMESTAMP evaluation: Oracle 9, ???
This is a nice chart. Oracle already has transaction start reported by
sysdate:
> SQL> begin
> 2 insert into rbr_foo select sysdate from dual;
> [...wait about 10 seconds...]
> 3 insert into rbr_foo select sysdate from dual;
> 4 end;
> 5 /
>
> PL/SQL procedure successfully completed.
>
> SQL> select * from rbr_foo;
>
> A
> ---------------------
> SEP 27, 2002 12:57:27
> SEP 27, 2002 12:57:27
so for CURRENT_TIMESTAMP it seems they have evaluation-time, while
MSSQL/Interbase have statement time.
> Given what Tom has posted regarding the standard, I think Oracle
> is wrong. I'm wondering how the others handle multiple
> references in CURRENT_TIMESTAMP in a single stored
> procedure/function invocation. It seems to me that the lower
> bound is #4, not #5, and the upper bound is implementation
> dependent. Therefore PostgreSQL is in compliance, but its
> compliance is not very popular.
I don't see how we can be compliant if SQL92 says:
The time of evaluation of the <datetime value function> during theexecution of the SQL-statement is
implementation-dependent.
It says it has to be "during the SQL statement", or is SQL statement
also ambiguous? Is that why Oracle did what they did?
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
