Thread: rule for update view that updates/inserts into 2 tables
<div class="Section1"><p class="MsoNormal">I would like to create a rule that, by updating a view, allows me to update
onetable and 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"> </div>
(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
Robins and Martijn,
Thanks for the help. My main objective here was to be able to perform both an update and an insert in one call (wanted to reduce database roundtrips, and speed up my program), and I am using the view solely to allow me to do this. What both of you have pointed out is that the behavior I’m seeing is a natural result of the way I’ve defined the view.
I don’t need to define the view the way I did in my example. I do need the view to have fields that map to fields in my_audit_table in order to do an insert into that table with values from the insert statement.
Here’s one solution that seems to work. Change my view to the following, leave rules the same as before:
--Create View
CREATE OR REPLACE VIEW my_view AS
SELECT
t.my_table_id,
t.a,
t.b,
bigint '0' as audit_id,
varchar(255) '' as c
FROM
my_table t;
Does this seem reasonable to you? Do you problems with this approach?
Thanks again for your help,
Chad
From: Robins Tharakan [mailto:tharakan@gmail.com]
Sent: Monday, April 14, 2008 8:34 PM
To: Chad Showalter
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] rule for update view that updates/inserts into 2 tables
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=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
On Mon, Apr 14, 2008 at 10:17 PM, Chad Showalter <cshowalter@bplglobal.net> 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 = 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