Re: rule for update view that updates/inserts into 2 tables

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(at)gmail(dot)com]
Sent: Monday, April 14, 2008 8:34 PM
To: Chad Showalter
Cc: pgsql-sql(at)postgresql(dot)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(at)bplglobal(dot)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