NEW in Rule makes another nextval call?

From: Sven Willenberger <sven(at)dmv(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: NEW in Rule makes another nextval call?
Date: 2005-10-20 13:33:02
Message-ID: 1129815182.21857.16.camel@lanshark.dmv.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On a table ("customer") I have a rule set up that is designed to update
a "contacts" table with a customer id once the customer is added to the
customer table. (Yes, this does seem backwards but it has to do with the
way this system of web-based signups gets translated into a customer
record).

CREATE TABLE customer (
custid serial primary key,
custname text not null,
startdate timestamp,
agent int);

CREATE RULE newcustomer AS
ON INSERT TO customer DO
UPDATE contacts SET custid = NEW.custid
WHERE contact.custname = NEW.custname;

Now when a new record is inserted into customer it gets a custid from
the nextval(<sequence name>) call; let's say it gets the value 296. What
I notice is that in the contacts table, the customer id ends up being
297; it's as if the rule's SET command is using the nextval() call
instead of the value retrieved by the actual insert command when
updating the contacts table. Is this intended behavior? or is the NEW
acting as a macro that is replace by "nextval(<sequence name>)" ?

Sven

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Michael Glaesemann 2005-10-20 13:40:46 Re: How to check is the table system
Previous Message Jan Wieck 2005-10-20 12:14:57 Re: [HACKERS] 'a' == 'a '