|
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
= 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
= new.a, b
= new.b WHERE my_table_id
= 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 to my_audit_table --doesn't work as I had anticipated. update my_view set a = 'new a contents', b = 'new b
contents', c = 'new c contents' where my_table_id = 1; -- If I execute the above update statement multiple times,
multiple rows will be --inserted with each call after the first call. -- --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 --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 use a sp. Thanks, Chad |
