stored procedure and timestamp

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
Thread:
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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message will trillich 2001-03-29 00:35:42 composite data types
Previous Message Tom Lane 2001-03-28 23:41:49 Re: Unexplained behaviour