Re: 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
>
>
>