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

rule for update view that updates/inserts into 2 tables

From: "Chad Showalter" <cshowalter(at)bplglobal(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: rule for update view that updates/inserts into 2 tables
Date: 2008-04-14 16:47:30
Message-ID: 001501c89e4f$3be75340$b3b5f9c0$@net (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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

 

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2008-04-14 16:48:19
Subject: Re: [HACKERS] Remove lossy-operator RECHECK flag?
Previous:From: Alvaro HerreraDate: 2008-04-14 16:45:40
Subject: Re: Lessons from commit fest

pgsql-sql by date

Next:From: Robins TharakanDate: 2008-04-15 00:33:55
Subject: Re: rule for update view that updates/inserts into 2 tables
Previous:From: Emi LuDate: 2008-04-14 13:41:41
Subject: export CSV file through Java JDBC

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