Thread: NEW in Rule makes another nextval call?

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

Sven Willenberger wrote:
>  Is this intended behavior? or is the NEW
> acting as a macro that is replace by "nextval(<sequence name>)" ?

Well, it's understood behaviour even if not quite "intended".

You are quite right, rules basically act like macros with all the
limitations they have. What is actually happening behind the scenes is
that the query is being rewritten to alter the query-plan tree. You
should be able to get your rule to work by referring to
currval(<sequence-name>) rather than NEW.custid.

However, in this particular case I think you want an after insert
trigger on customer rather than a rule.

--
   Richard Huxton
   Archonet Ltd

On Thu, 2005-10-20 at 15:01 +0100, Richard Huxton wrote:
> Sven Willenberger wrote:
> >  Is this intended behavior? or is the NEW
> > acting as a macro that is replace by "nextval(<sequence name>)" ?
>
> Well, it's understood behaviour even if not quite "intended".
>
> You are quite right, rules basically act like macros with all the
> limitations they have. What is actually happening behind the scenes is
> that the query is being rewritten to alter the query-plan tree. You
> should be able to get your rule to work by referring to
> currval(<sequence-name>) rather than NEW.custid.
>
> However, in this particular case I think you want an after insert
> trigger on customer rather than a rule.
>

As as AFTER INSERT trigger, I can safely assume here that NEW.custid wil
now properly use the actual value of the custid rather than nextval()? I
have dropped the rule and created the AFTER INSERT trigger so I guess I
will find out shortly enough :-)

Thanks,

Sven

Sven Willenberger wrote:
> On Thu, 2005-10-20 at 15:01 +0100, Richard Huxton wrote:
>>However, in this particular case I think you want an after insert
>>trigger on customer rather than a rule.
>
> As as AFTER INSERT trigger, I can safely assume here that NEW.custid wil
> now properly use the actual value of the custid rather than nextval()? I
> have dropped the rule and created the AFTER INSERT trigger so I guess I
> will find out shortly enough :-)

An AFTER TRIGGER does nothing to the plan-tree, it just calls a function
once per row (for a per-row trigger). You have two structures (NEW,OLD)
with ordinary values in them.

--
   Richard Huxton
   Archonet Ltd