Re: rule for update view that updates/inserts into 2 tables - Mailing list pgsql-sql
| From | Robins Tharakan |
|---|---|
| Subject | Re: rule for update view that updates/inserts into 2 tables |
| Date | |
| Msg-id | 36af4bed0804141733t14d01791t1b7499ed3b986484@mail.gmail.com Whole thread Raw |
| In response to | rule for update view that updates/inserts into 2 tables ("Chad Showalter" <cshowalter@bplglobal.net>) |
| Responses |
Re: rule for update view that updates/inserts into 2 tables
("Chad Showalter" <cshowalter@bplglobal.net>)
|
| List | pgsql-sql |
(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=1' the RULE now inserts another row with my_table_id = 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 = au.my_table_id
AND au.audit_id = (SELECT max(audit_id) FROM my_audit_table WHERE au.my_table_id = 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
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