Thread: Update and Insert in a View Insert Rule

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?

thanks for your time
--

Tom Jenkins
Development InfoStructure
http://www.devis.com

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 (
 ...
);

On Tue, 2002-05-21 at 18:19, Stephan Szabo wrote:
> On 21 May 2002, Tom Jenkins wrote:
> >
> > 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 (
>  ...
> );
>

AAAARRRRRGGGGGGHHHHHHH!!!
*sigh* up that was the problem.  Funny how you can stare at something
and not actually see it.  Thanks


--

Tom Jenkins
Development InfoStructure
http://www.devis.com