Обсуждение: rule for update view that updates/inserts into 2 tables

<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> 

On Mon, Apr 14, 2008 at 06:03:27PM -0400, Chad Showalter wrote:
> 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.

I saw it there too, but couldn't spot anything at the time. But I think
I have something now. You say at one point:
> Specifically,
> .         after the first call, 1 row is inserted
> .         after the second call, 2 rows are inserted
> .         after the third call, 4 rows are inserted

Looking at your view definition:
> CREATE OR REPLACE VIEW my_view AS
> SELECT t.my_table_id,t.a,t.b,au.audit_id, au.c
> FROM
>                 my_table t, my_audit_table au
> WHERE
>                 t.my_table_id = au.my_table_id;

ISTM that you will get one row in the output for each row in your audit
table, which means indeed after the first update you will now have two
rows in the view for that id and the rule is applied to the result of
the view, which means you get lots of duplicate inserts.

My question is, why do you join to the audit table in the view anyway,
it seems somewhat pointless. But if you really want that, I'd suggest
adding something to the view to limit the number of audit entries to one.

> How do I accomplish what I want to accomplish here?  I'd prefer not to use a
> sp.

Why not?

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.