From: | Tom Jenkins <tjenkins(at)devis(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Update and Insert in a View Insert Rule |
Date: | 2002-05-21 17:18:39 |
Message-ID: | 1022001520.20462.258.camel@asimov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Marc G. Fournier | 2002-05-21 17:52:31 | Re: Is Digest Mode Really Available? |
Previous Message | Thomas Lockhart | 2002-05-21 16:21:30 | Re: [GENERAL] Psql 7.2.1 Regress tests failed on RedHat 7.3 |