pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedrule for update view that updates/inserts into 2 tables
4+ messages / 3 participants
[nested] [flat]
* rule for update view that updates/inserts into 2 tables
@ 2008-04-14 16:47 Chad Showalter <cshowalter@bplglobal.net>
2008-04-15 00:33 ` Re: rule for update view that updates/inserts into 2 tables Robins Tharakan <tharakan@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Chad Showalter @ 2008-04-14 16:47 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: rule for update view that updates/inserts into 2 tables
2008-04-14 16:47 rule for update view that updates/inserts into 2 tables Chad Showalter <cshowalter@bplglobal.net>
@ 2008-04-15 00:33 ` Robins Tharakan <tharakan@gmail.com>
2008-04-15 15:27 ` Re: rule for update view that updates/inserts into 2 tables Chad Showalter <cshowalter@bplglobal.net>
0 siblings, 1 reply; 4+ messages in thread
From: Robins Tharakan @ 2008-04-15 00:33 UTC (permalink / raw)
To: Chad Showalter <cshowalter@bplglobal.net>; +Cc: pgsql-sql
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
>
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: rule for update view that updates/inserts into 2 tables
2008-04-14 16:47 rule for update view that updates/inserts into 2 tables Chad Showalter <cshowalter@bplglobal.net>
2008-04-15 00:33 ` Re: rule for update view that updates/inserts into 2 tables Robins Tharakan <tharakan@gmail.com>
@ 2008-04-15 15:27 ` Chad Showalter <cshowalter@bplglobal.net>
2008-04-15 15:41 ` Re: [SQL] rule for update view that updates/inserts into 2 tables Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 1 reply; 4+ messages in thread
From: Chad Showalter @ 2008-04-15 15:27 UTC (permalink / raw)
To: 'Robins Tharakan' <tharakan@gmail.com>; 'Martijn van Oosterhout' <kleptog@svana.org>; +Cc: pgsql-sql; pgsql-hackers@postgresql.org
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [SQL] rule for update view that updates/inserts into 2 tables
2008-04-14 16:47 rule for update view that updates/inserts into 2 tables Chad Showalter <cshowalter@bplglobal.net>
2008-04-15 00:33 ` Re: rule for update view that updates/inserts into 2 tables Robins Tharakan <tharakan@gmail.com>
2008-04-15 15:27 ` Re: rule for update view that updates/inserts into 2 tables Chad Showalter <cshowalter@bplglobal.net>
@ 2008-04-15 15:41 ` Martijn van Oosterhout <kleptog@svana.org>
0 siblings, 0 replies; 4+ messages in thread
From: Martijn van Oosterhout @ 2008-04-15 15:41 UTC (permalink / raw)
To: Chad Showalter <cshowalter@bplglobal.net>; +Cc: Robins Tharakan <tharakan@gmail.com>; pgsql-sql; pgsql-hackers@postgresql.org
On Tue, Apr 15, 2008 at 11:27:15AM -0400, Chad Showalter wrote:
> 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.
Right.
> Here's one solution that seems to work. Change my view to the following,
> leave rules the same as before:
Looks good, though I would've used NULLs instead of actual values, but
it doesn't matter.
The reason I was asking about the SP is that you could do:
SELECT insert_row(a,b,c);
With the appropriate procedure definition and it would still have one
roundtrip. But your way works also.
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.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-04-15 15:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-14 16:47 rule for update view that updates/inserts into 2 tables Chad Showalter <cshowalter@bplglobal.net>
2008-04-15 00:33 ` Robins Tharakan <tharakan@gmail.com>
2008-04-15 15:27 ` Chad Showalter <cshowalter@bplglobal.net>
2008-04-15 15:41 ` Martijn van Oosterhout <kleptog@svana.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox