Re: Rule uses wrong value

Tom Lane wrote:

>Jeff Boes <jboes@nexcerpt.com> writes:
>  
>
>>Tom Lane wrote:
>>    
>>
>>>No kidding.  A rule is a macro and therefore has the usual risks of
>>>multiple evaluations of arguments.
>>>      
>>>
>
>  
>
>>But shouldn't "new.job_id" use the value that was already recorded in 
>>the original row?
>>    
>>
>
>There is no "value that was already recorded in the original row";
>if you want to think in those terms you should use a trigger.  It's
>fundamentally wrong to think of a rule in that way.
>
>In the rule, "new.job_id" is effectively a macro parameter that gets
>replaced by the INSERT's corresponding expression, ie, nextval(...).
>
>			regards, tom lane
>  
>
Aha! Well, that certainly wasn't clear from the documentation:

http://www.postgresql.org/docs/7.4/static/sql-createrule.html

"... the special table names NEW and OLD may be used to refer to values 
in the referenced table. NEW is valid in ON INSERT and ON UPDATE rules 
to refer to *the new row being inserted* or updated. OLD is valid in ON 
UPDATE and ON DELETE rules to refer to the existing row being updated or 
deleted."

To me, "new row" and "old row" imply what's already in the table.

On the other hand, I hadn't seen this before:

http://www.postgresql.org/docs/7.4/static/rules.html

"For INSERT commands, the target list describes the new rows that should 
go into the result relation. It consists of the *expressions in the 
VALUES clause* or the ones from the SELECT clause in INSERT ... SELECT. 
The first step of the rewrite process adds target list entries for any 
columns that were not assigned to by the original command but have 
defaults. Any remaining columns (with neither a given value nor a 
default) will be filled in by the planner with a constant null expression."

-- 
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise