Thread: stored procedure and timestamp

I have the following trigger/stored procedure:

CREATE FUNCTION memb_num () RETURNS opaque AS '
         BEGIN
                 NEW.member_num := date_part(''epoch'', timestamp ''now'');
                 RETURN NEW;
         END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER memb_num_trigger BEFORE INSERT
         ON members FOR EACH ROW
         EXECUTE PROCEDURE memb_num();


I get the following error when I attempt to insert any records beyond the
first:
"Cannot insert a duplicate key into unique index members_member_num_key"

So the function isn't inserting "live" timestamps. I believe this means the
function is not trying to set NEW.member_num to the timestamp of when the
function gets called, which is what I expected. I'm guessing the function
is evaluating the timestamp at the moment of adding the function to the
database - then inserting that stored timestamp repeatedly?

On Wed, Mar 28, 2001 at 04:13:19PM -0800, Soma Interesting wrote:
> I have the following trigger/stored procedure:
>
> CREATE FUNCTION memb_num () RETURNS opaque AS '
>          BEGIN
>                  NEW.member_num := date_part(''epoch'', timestamp ''now'');
                                                                ^^^^^^^^^^

> "Cannot insert a duplicate key into unique index members_member_num_key"


 From docs:

   It is quite important to realize that CURRENT_TIMESTAMP and related
   functions all return the time as of the start of the current transaction;
   their values do not increment while a transaction is running. But
   timeofday() returns the actual current time.


 CURRENT_TIMESTAMP = now() = timestamp 'now'


                                Karel



--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz