Mimicking Oracle SYSDATE

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

Responses

Browse pgsql-general by date

  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