Skip site navigation (1) Skip section navigation (2)

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

From: "Robins Tharakan" <tharakan(at)gmail(dot)com>
To: "Chad Showalter" <cshowalter(at)bplglobal(dot)net>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: rule for update view that updates/inserts into 2 tables
Date: 2008-04-15 00:33:55
Message-ID: 36af4bed0804141733t14d01791t1b7499ed3b986484@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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(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
>
>
>

In response to

Responses

pgsql-hackers by date

Next:From: Dawid KuroczkoDate: 2008-04-15 00:48:48
Subject: Re: Cached Query Plans
Previous:From: Alvaro HerreraDate: 2008-04-14 23:36:45
Subject: bug in localized \df+ output

pgsql-sql by date

Next:From: Chad ShowalterDate: 2008-04-15 15:27:15
Subject: Re: rule for update view that updates/inserts into 2 tables
Previous:From: Chad ShowalterDate: 2008-04-14 16:47:30
Subject: rule for update view that updates/inserts into 2 tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group