Re: Update and Insert in a View Insert Rule

On 21 May 2002, Tom Jenkins wrote:

> Hello all,
> I have a table jobs that holds both historical and current jobs:
> jobid SERIAL
> jobemployee INT4
> jobiscurrent INT2
> etc
>
> the users manipulate two views: historicaljob and currentjob. These
> views are simply defined by the value of jobiscurrent (0 for historical,
> 1 for current - yes i know it should be a boolean but erwin won't
> generate a postgres boolean may it rot in hell)
>
> now i have the insert rule working fine for historical jobs. however
> for currentjob, there is a small twist. First the old current job must
> be set to historical, then the new current job inserted.
>
> my insert rule is:
>
> CREATE RULE insert_current_job AS
> ON INSERT TO currentjob
> DO INSTEAD
> UPDATE job set jobiscurrent=0, lastuser=New.lastuser
> WHERE jobemployee = NEW.jobemployee and jobiscurrent=1;
> INSERT INTO job (
> jobemployee,
> jobagencybureau,
> jobbranch,
> blah, blah, blah,
> jobiscurrent
> ) VALUES (
> NEW.jobemployee,
> NEW.jobagencybureau,
> NEW.jobbranch,
> blah, blah, blah,
> 1
> )
>
> unfortunately this gives me an error when I attempt to load the rule I
> get:
> ERROR: NEW used in non-rule query
>
> I don't understand why this wouldn't work. Unless it has something to
> do with NEW getting "lost" in the update call? should i move the update
> out to a function and calling it from the rule?

I think you forgot to use the () around the multiple actions.
It should probably be:
DO INSTEAD (
...
);