Update and Insert in a View Insert Rule
От | Tom Jenkins |
---|---|
Тема | Update and Insert in a View Insert Rule |
Дата | |
Msg-id | 1022001520.20462.258.camel@asimov обсуждение исходный текст |
Ответы |
Re: Update and Insert in a View Insert Rule
|
Список | 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
В списке pgsql-general по дате отправления: