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 functionCREATE 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.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
