Hello,
We are thinking of building our own version of Oracle's sysdate, in
the form of PostgreSQL extension.
Consider the behavior of sysdate for multiple inserts inside a function
CREATE OR REPLACE FUNCTION fun2
RETURN number
IS var1 number(10); var2 number (2);
BEGIN
insert into t1 select sysdate from dual connect by rownum<=70000;
FOR var1 IN 0..1000000
LOOP
SELECT 0 iNTO var2 FROM dual;
END LOOP;
insert into t1 select sysdate from dual connect by rownum<=70000;
RETURN var1;
END;
The result of all first 70000 rows are same and result of all second
70000 row are same. But there is a difference between the values
returned by sysdate between the 2 loops.
PostgreSQL's statement_timestamp is not a good substitute for sysdate
in this case
as it returns same value for both loops.
However if there are multiple inserts outside a function
statement_timestamp(0) it seems to work the same as sysdate.
Our implementation sysdate hence needs to figure out the context in
which its called
i.e. within a function or from outside.
Also we have a similar need to figure out if its been called for
multiple inserts (in a loop or simple insert statements one after
another) as this affects behavior as well.
So in short if we have a custom function how do we figure out
1) if its called from within another function
2) called within a loop
3) called multiple times outside a loop
since this would alter what it returns
Any idea how to implement this?
regards
Sameer
