Lists: | pgsql-general |
---|
From: | Soma Interesting <dfunct(at)telus(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | stored procedure and timestamp |
Date: | 2001-03-29 00:13:19 |
Message-ID: | 5.0.2.1.0.20010328155912.026bfe90@pop.telus.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
From: | Karel Zak <zakkr(at)zf(dot)jcu(dot)cz> |
---|---|
To: | Soma Interesting <dfunct(at)telus(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: stored procedure and timestamp |
Date: | 2001-03-29 09:30:29 |
Message-ID: | 20010329113029.B19743@ara.zf.jcu.cz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/
C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz