From: | Sameer Thakur <samthakur74(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Mimicking Oracle SYSDATE |
Date: | 2014-08-19 14:11:00 |
Message-ID: | CABzZFEt-R8F5tdBk-uBShg0vgVkvu0vD4KLA_BgOtbSY6wD5BA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2014-08-19 14:21:31 | Re: Mimicking Oracle SYSDATE |
Previous Message | Larry White | 2014-08-19 12:31:54 | Re: New wrapper library: QUINCE |