------=_Part_22136_17421385.1208219635842
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Hi Chad,
(Pardon me if I am shooting the stars here...)
Don't you think that on each update, you would be creating a new row that
satisfies that very given condition for the view ?
By that I mean that when you do a 'UPDATE... WHERE my_table_id=3D1' the
RUL=
E
now inserts another row with my_table_id =3D 1... which now makes two rows
that satisfy the criteria for the view.
The second time you run the update, the RULE inserts a row (in the
my_audit_table) for each row found (on the second run it'd be two rows)
...
and then so on .
Therefore, you probably want to use this CREATE RULE query instead...
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 =3D au.my_table_id
AND au.audit_id =3D (SELECT max(audit_id) FROM my_audit_table WHERE
au.my_table_id =3D my_audit_table.my_table_id);
Of course this brings us into another problem that the INSERT / UPDATE
statements bomb because of the aggregate that is now there in the view...
and then I am drawing a blank here !
(Note: As mentioned in PG Docs, I have already tried creating a blanket DO
NOTHING rule coupled with Chad's rule as a DO ALSO rule ... but that
doesn'=
t
work either)
Anyone else with some ideas ?
*Robins*
On Mon, Apr 14, 2008 at 10:17 PM, Chad Showalter
<cshowalter@[EMAIL PROTECTED]
>
wrote:
> I would like to create a rule that, by updating a view, allows me to
> update one table and insert into another.
>
>
>
> The following example illustrates what I'm trying to do:
>
>
>
> --Create Tables
>
> CREATE TABLE my_table
>
> (
>
> my_table_id serial,
>
> a character varying(255),
>
> b character varying(255),
>
> CONSTRAINT my_table_id_pk PRIMARY KEY (my_table_id)
>
> );
>
>
>
> CREATE TABLE my_audit_table
>
> (
>
> audit_id serial,
>
> my_table_id int,
>
> c character varying(255),
>
> CONSTRAINT audit_id_pk PRIMARY KEY (audit_id)
>
> );
>
>
>
> --Create View
>
> 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 =3D au.my_table_id;
>
>
>
> --Create Rules
>
> CREATE OR REPLACE RULE insert_to_my_view AS
>
> ON INSERT TO my_view
>
> DO INSTEAD(
>
> INSERT INTO my_table (a,b)
>
> VALUES(new.a, new.b);
>
> INSERT INTO my_audit_table(my_table_id, c)
>
> VALUES
>
> (currval('my_table_my_table_id_seq'), new.c);
>
> );
>
>
>
> CREATE OR REPLACE RULE update_my_view AS
>
> ON UPDATE TO my_view DO INSTEAD
>
> ( UPDATE my_table SET
>
> a =3D new.a,
>
> b =3D new.b
>
> WHERE
>
> my_table_id =3D old.my_table_id;
>
> INSERT INTO my_audit_table
>
> (my_table_id,
>
> c)
>
> VALUES
>
> (new.my_table_id,
>
> new.c);
>
> );
>
>
>
> --The insert statement below inserts one row into my_table, and one row
> into my_audit_table
>
> --(This works the way I would like)
>
> insert into my_view(a,b,c) values('a contents','b contents', 'c
> contents');
>
>
>
> --The update statement below doesn't work the way I want.
>
> --What I would like this to do is to update one row in my_table, and
> insert
>
> --one row into my_audit table. It does the update fine, but the insert
t=
o
> my_audit_table
>
> --doesn't work as I had anticipated.
>
> update my_view set a =3D 'new a contents', b =3D 'new b contents', c
=3D=
'new c
> contents' where my_table_id =3D 1;
>
>
>
>
>
>
>
>
>
> If I execute the above update statement multiple times, multiple rows
wil=
l
> be
>
> inserted with each call after the first call.
>
>
>
> Specifically,
>
> =B7 after the first call, 1 row is inserted
>
> =B7 after the second call, 2 rows are inserted
>
> =B7 after the third call, 4 rows are inserted
>
> =B7 after the fourth call, 8 rows are inserted... and so on
>
>
>
> The problem is due to the INSERT in the update_my_view rule:
>
>
>
> INSERT INTO my_audit_table
>
> (my_table_id,
>
> c)
>
> VALUES
>
> (new.my_table_id,
>
> new.c);
>
>
>
> Apparently, "new.my_table_id" in this case references more than one row,
> if more than one row with
>
> the given id already exists in my_audit_table.
>
>
>
> How do I accomplish what I want to accomplish here? I'd prefer not to
us=
e
> a sp.
>
>
>
> Thanks,
>
> Chad
>
>
>
------=_Part_22136_17421385.1208219635842
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Hi Chad,<br><br>(Pardon me if I am shooting the stars
here...)<br><br>Don&#=
39;t you think that on each update, you would be creating a new row that
sa=
tisfies that very given condition for the view ?<br><br>By that I mean
that=
when you do a 'UPDATE... WHERE my_table_id=3D1' the RULE now
inser=
ts another row with my_table_id =3D 1... which now makes two rows that
sati=
sfy the criteria for the view.<br>
<br>The second time you run the update, the RULE inserts a row (in the
my_a=
udit_table) for each row found (on the second run it'd be two rows)
...=
and then so on .<br><br>Therefore, you probably want to use this CREATE
RU=
LE query instead...<br>
<br>CREATE OR REPLACE VIEW my_view AS <br> SELECT t.my_table_id, t.a,
=
t.b, au.audit_id, au.c<br> FROM my_table t, my_audit_table
au<b=
r> WHERE t.my_table_id =3D au.my_table_id<br> AND au.audit_id
=
=3D (SELECT max(audit_id) FROM my_audit_table WHERE au.my_table_id =3D
my_a=
udit_table.my_table_id);<br>
<br>Of course this brings us into another problem that the INSERT / UPDATE
=
statements bomb because of the aggregate that is now there in the view...
a=
nd then I am drawing a blank here !<br><br>(Note: As mentioned in PG Docs,
=
I have already tried creating a blanket DO NOTHING rule coupled with
Chad&#=
39;s rule as a DO ALSO rule ... but that doesn't work either)<br>
<br>Anyone else with some ideas ? <br>
<br><b>Robins</b><br><br><div class=3D"gmail_quote">On Mon, Apr 14, 2008
at=
10:17 PM, Chad Showalter <<a
href=3D"mailto:cshowalter@[EMAIL PROTECTED]
">c=
showalter@[EMAIL PROTECTED]
>> wrote:<br><blockquote
class=3D"gmail_quote"=
style=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt
0.=
8ex; padding-left: 1ex;">
<div link=3D"blue" vlink=3D"purple" lang=3D"EN-US">
<div>
<p>I would like to create a rule that, by updating a view,
allows me to update one table and insert into another.</p>
<p> </p>
<p>The following example illustrates what I'm trying to
do:</p>
<p> </p>
<p>--Create Tables</p>
<p>CREATE TABLE my_table </p>
<p>(</p>
<p> =
my_table_id serial,</p>
<p> =
a character varying(255),</p>
<p> =
b character varying(255),</p>
<p>CONSTRAINT my_table_id_pk PRIMARY KEY (my_table_id)</p>
<p>);</p>
<p> </p>
<p>CREATE TABLE my_audit_table</p>
<p>(</p>
<p> =
audit_id serial,</p>
<p> =
my_table_id int,</p>
<p> =
c character varying(255),</p>
<p>CONSTRAINT audit_id_pk PRIMARY KEY (audit_id)</p>
<p>);</p>
<p> </p>
<p>--Create View</p>
<p>CREATE OR REPLACE VIEW my_view AS</p>
<p>SELECT
&nb=
sp;
</p>
<p>t.my_table_id,</p>
<p>t.a,</p>
<p>t.b,</p>
<p>au.audit_id,</p>
<p>au.c</p>
<p>FROM</p>
<p> =
my_table t, my_audit_table au</p>
<p>WHERE</p>
<p> =
t.my_table_id =3D au.my_table_id;</p>
<p> </p>
<p>--Create Rules</p>
<p>CREATE OR REPLACE RULE insert_to_my_view AS </p>
<p>ON INSERT TO my_view </p>
<p>DO INSTEAD( </p>
<p>INSERT INTO my_table (a,b)</p>
<p>VALUES(new.a, new.b); </p>
<p>INSERT INTO my_audit_table(my_table_id, c) </p>
<p>VALUES </p>
<p>(currval('my_table_my_table_id_seq'), new.c); </p>
<p>);</p>
<p> </p>
<p>CREATE OR REPLACE RULE update_my_view AS </p>
<p>ON UPDATE TO my_view DO INSTEAD </p>
<p> ( UPDATE my_table SET </p>
<p> =
a =3D new.a, </p>
<p> =
b =3D new.b </p>
<p>WHERE </p>
<p> =
my_table_id =3D old.my_table_id; </p>
<p>INSERT INTO my_audit_table </p>
<p> =
(my_table_id, </p>
<p> =
c) </p>
<p>VALUES </p>
<p> =
(new.my_table_id, </p>
<p> =
new.c); </p>
<p>);</p>
<p> </p>
<p>--The insert statement below inserts one row into my_table,
and one row into my_audit_table</p>
<p>--(This works the way I would like)</p>
<p>insert into my_view(a,b,c) values('a contents','b
contents&#=
39;,
'c contents');</p>
<p> </p>
<p>--The update statement below doesn't work the way I
want.</p>
<p>--What I would like this to do is to update one row in
my_table, and insert</p>
<p>--one row into my_audit table. It does the update
fine, but the insert to my_audit_table</p>
<p>--doesn't work as I had anticipated. </p>
<p>update my_view set a =3D 'new a contents', b =3D 'new b
contents', c =3D 'new c contents' where my_table_id =3D
1=
;</p>
<p> </p>
<p> </p>
<p> </p>
<p> </p>
<p>If I execute the above update statement multiple times,
multiple rows will be </p>
<p>inserted with each call after the first call.</p>
<p> </p>
<p>Specifically,</p>
<p style=3D"text-indent: -0.25in;"><span style=3D"font-family:
Symbol;"><sp=
an>=B7<span>
</span></span></span>after the first call, 1 row is inserted</p>
<p style=3D"text-indent: -0.25in;"><span style=3D"font-family:
Symbol;"><sp=
an>=B7<span>
</span></span></span>after the second call, 2 rows are inserted</p>
<p style=3D"text-indent: -0.25in;"><span style=3D"font-family:
Symbol;"><sp=
an>=B7<span>
</span></span></span>after the third call, 4 rows are inserted</p>
<p style=3D"text-indent: -0.25in;"><span style=3D"font-family:
Symbol;"><sp=
an>=B7<span>
</span></span></span>after the fourth call, 8 rows are inserted...
and so on</p>
<p> </p>
<p>The problem is due to the INSERT in the update_my_view rule:</p>
<p> </p>
<p>INSERT INTO my_audit_table </p>
<p>
(my_table_id, </p>
<p>
c) </p>
<p>VALUES </p>
<p>
(new.my_table_id, </p>
<p>
new.c);</p>
<p> </p>
<p>Apparently, "new.my_table_id" in this case
references more than one row, if more than one row with </p>
<p>the given id already exists in my_audit_table.</p>
<p> </p>
<p>How do I accomplish what I want to accomplish here?
I'd prefer not to use a sp.</p>
<p> </p>
<p>Thanks,</p>
<p>Chad</p>
<p> </p>
</div>
</div>
</blockquote></div><br>
------=_Part_22136_17421385.1208219635842--
|
