Re: Mimicking Oracle SYSDATE

On 08/19/2014 07:11 AM, Sameer Thakur wrote:
> 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?

Yes, clock_timestamp():

http://www.postgresql.org/docs/9.3/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

> regards
> Sameer
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com