Re: Rule uses wrong value

Tom Lane wrote:

>Jeff Boes <jboes(at)nexcerpt(dot)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