rule for update view that updates/inserts into 2 tables - Mailing list pgsql-hackers

<div class="Section1"><p class="MsoNormal">I’ve posted this on pgsql-general and pgsql-sql, and haven’t got any
responses. If any of you would be able to take a look at this for me and give some feedback, I’d be obliged…<p
class="MsoNormal"> <pclass="MsoNormal">I would like to create a rule that, by updating a view, allows me to update one
tableand insert into another.<p class="MsoNormal"> <p class="MsoNormal">The following example illustrates what I’m
tryingto do:<p class="MsoNormal"> <p class="MsoNormal">--Create Tables<p class="MsoNormal">CREATE TABLE my_table <p
class="MsoNormal">(<pclass="MsoNormal">                my_table_id serial,<p class="MsoNormal">                a
charactervarying(255),<p class="MsoNormal">                b character varying(255),<p class="MsoNormal">CONSTRAINT
my_table_id_pkPRIMARY KEY (my_table_id)<p class="MsoNormal">);<p class="MsoNormal"> <p class="MsoNormal">CREATE TABLE
my_audit_table<pclass="MsoNormal">(<p class="MsoNormal">                audit_id serial,<p
class="MsoNormal">               my_table_id int,<p class="MsoNormal">                c character varying(255),<p
class="MsoNormal">CONSTRAINTaudit_id_pk PRIMARY KEY (audit_id)<p class="MsoNormal">);<p class="MsoNormal"> <p
class="MsoNormal">--CreateView<p class="MsoNormal">CREATE OR REPLACE VIEW my_view AS<p class="MsoNormal">SELECT
                <p class="MsoNormal">t.my_table_id,<p class="MsoNormal">t.a,<p class="MsoNormal">t.b,<p
class="MsoNormal">au.audit_id,<pclass="MsoNormal">au.c<p class="MsoNormal">FROM<p class="MsoNormal">               
my_tablet, my_audit_table au<p class="MsoNormal">WHERE<p class="MsoNormal">                t.my_table_id =
au.my_table_id;<pclass="MsoNormal"> <p class="MsoNormal">--Create Rules<p class="MsoNormal">CREATE OR REPLACE RULE
insert_to_my_viewAS <p class="MsoNormal">ON INSERT TO my_view <p class="MsoNormal">DO INSTEAD(  <p
class="MsoNormal">INSERTINTO my_table (a,b)<p class="MsoNormal">VALUES(new.a, new.b);  <p class="MsoNormal">INSERT INTO
my_audit_table(my_table_id,c)  <p class="MsoNormal">VALUES  <p class="MsoNormal">(currval('my_table_my_table_id_seq'),
new.c);<p class="MsoNormal">);<p class="MsoNormal"> <p class="MsoNormal">CREATE OR REPLACE RULE update_my_view AS <p
class="MsoNormal">ONUPDATE TO my_view DO INSTEAD  <p class="MsoNormal"> ( UPDATE my_table SET  <p
class="MsoNormal">               a = new.a,  <p class="MsoNormal">                b = new.b   <p
class="MsoNormal">WHERE <p class="MsoNormal">                my_table_id = old.my_table_id;  <p
class="MsoNormal">INSERTINTO my_audit_table <p class="MsoNormal">                 (my_table_id,  <p
class="MsoNormal">                c)  <p class="MsoNormal">VALUES  <p class="MsoNormal">               
 (new.my_table_id, <p class="MsoNormal">                 new.c); <p class="MsoNormal">);<p class="MsoNormal"> <p
class="MsoNormal">--Theinsert statement below inserts one row into my_table, and one row into my_audit_table<p
class="MsoNormal">--(Thisworks the way I would like)<p class="MsoNormal">insert into my_view(a,b,c) values('a
contents','bcontents', 'c contents');<p class="MsoNormal"> <p class="MsoNormal">--The update statement below doesn’t
workthe way I want.<p class="MsoNormal">--What I would like this to do is to update one row in my_table, and insert<p
class="MsoNormal">--onerow into my_audit table.  It does the update fine, but the insert to my_audit_table<p
class="MsoNormal">--doesn'twork as I had anticipated.  <p class="MsoNormal">update my_view set a = 'new a contents', b
='new b contents', c  = 'new c contents' where my_table_id = 1;<p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal"> <pclass="MsoNormal"> <p class="MsoNormal">If I execute the above update statement multiple times,
multiplerows will be <p class="MsoNormal">inserted with each call after the first call.<p class="MsoNormal"> <p
class="MsoNormal">Specifically,<pclass="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo1"><span
style="font-family:Symbol"><spanstyle="mso-list:Ignore">·<span style="font:7.0pt "Times New Roman"">        
</span></span></span>afterthe first call, 1 row is inserted<p class="MsoListParagraph"
style="text-indent:-.25in;mso-list:l0level1 lfo1"><span style="font-family:Symbol"><span style="mso-list:Ignore">·<span
style="font:7.0pt"Times New Roman"">         </span></span></span>after the second call, 2 rows are inserted<p
class="MsoListParagraph"style="text-indent:-.25in;mso-list:l0 level1 lfo1"><span style="font-family:Symbol"><span
style="mso-list:Ignore">·<spanstyle="font:7.0pt "Times New Roman"">         </span></span></span>after the third call,
4rows are inserted<p class="MsoListParagraph" style="text-indent:-.25in;mso-list:l0 level1 lfo1"><span
style="font-family:Symbol"><spanstyle="mso-list:Ignore">·<span style="font:7.0pt "Times New Roman"">        
</span></span></span>afterthe fourth call, 8 rows are inserted... and so on<p class="MsoNormal"> <p
class="MsoNormal">Theproblem is due to the INSERT in the update_my_view rule:<p class="MsoNormal"> <p
class="MsoNormal">INSERTINTO my_audit_table <p class="MsoNormal">             (my_table_id,  <p
class="MsoNormal">           c)  <p class="MsoNormal">VALUES  <p class="MsoNormal">             (new.my_table_id,  <p
class="MsoNormal">            new.c);<p class="MsoNormal"> <p class="MsoNormal">Apparently, "new.my_table_id" in this
casereferences more than one row, if more than one row with <p class="MsoNormal">the given id already exists in
my_audit_table.<pclass="MsoNormal"> <p class="MsoNormal">How do I accomplish what I want to accomplish here?  I'd
prefernot to use a sp.<p class="MsoNormal"> <p class="MsoNormal">Thanks,<p class="MsoNormal">Chad<p
class="MsoNormal"> <pclass="MsoNormal"> </div>