Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 9372A2E0040 for ; Mon, 14 Apr 2008 13:46:37 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 24887-04 for ; Mon, 14 Apr 2008 13:46:25 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.bplglobal.net (mail.bplglobal.net [81.27.20.134]) by developer.postgresql.org (Postfix) with ESMTP id 4003C2E0056 for ; Mon, 14 Apr 2008 13:46:25 -0300 (ADT) Received: (qmail 32173 invoked from network); 14 Apr 2008 16:37:48 -0000 Received: from unknown (HELO mail.tech.bplglobal.eu) ([127.0.0.2]) (envelope-sender ) by mail.bplglobal.eu (qmail-ldap-1.03) with SMTP for ; 14 Apr 2008 16:37:48 -0000 Received: from mail.tech.bplglobal.eu (mail.tech.bplglobal.eu [127.0.0.1]) by localhost (Postfix) with SMTP id 6CF5B68003 for ; Mon, 14 Apr 2008 18:37:48 +0200 (CEST) Received: from mail.bplglobal.net (mail.tech.bplglobal.eu [127.0.0.1]) by localhost (Postfix) with ESMTP id 4BB3E68002 for ; Mon, 14 Apr 2008 18:37:48 +0200 (CEST) Received: (qmail 32158 invoked from network); 14 Apr 2008 16:37:45 -0000 Received: from unknown (HELO bplgcshowalter) (cshowalter@bplglobal.net@[74.219.149.162]) (envelope-sender ) by mail.bplglobal.eu (qmail-ldap-1.03) with SMTP for ; 14 Apr 2008 16:37:45 -0000 From: "Chad Showalter" To: Subject: rule for update view that updates/inserts into 2 tables Date: Mon, 14 Apr 2008 12:47:30 -0400 Message-ID: <001501c89e4f$3be75340$b3b5f9c0$@net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0016_01C89E2D.B4D5B340" X-Mailer: Microsoft Office Outlook 12.0 Content-Language: en-us Thread-Index: AcieTzibHyLY2N73RSWP0CkhpjckEA== X-BPLGLOBAL-Version: 5.3.1.294258, Antispam-Engine: 2.6.0.325393, Antispam-Data: 2008.4.14.93229 X-BPLGLOBAL-Spam: Mail Whitelisted X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200804/82 X-Sequence-Number: 30621 This is a multipart message in MIME format. ------=_NextPart_000_0016_01C89E2D.B4D5B340 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0016_01C89E2D.B4D5B340 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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

(

         &= nbsp;      my_table_id serial,

         &= nbsp;      a character varying(255),

         &= nbsp;      b character varying(255),

CONSTRAINT my_table_id_pk PRIMARY KEY = (my_table_id)

);

 

CREATE TABLE my_audit_table

(

         &= nbsp;      audit_id serial,

         &= nbsp;      my_table_id int,

         &= nbsp;      c character varying(255),

CONSTRAINT audit_id_pk PRIMARY KEY = (audit_id)

);

 

--Create View

CREATE OR REPLACE VIEW my_view AS

SELECT             &= nbsp;   

t.my_table_id,

t.a,

t.b,

au.audit_id,

au.c

FROM

         &= nbsp;      my_table t, my_audit_table au

WHERE

         &= nbsp;      t.my_table_id =3D 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 

         &= nbsp;      a =3D new.a, 

         &= nbsp;      b =3D new.b  

WHERE 

         &= nbsp;      my_table_id =3D old.my_table_id; 

INSERT INTO my_audit_table

         &= nbsp;       (my_table_id, 

         &= nbsp;       c) 

VALUES 

         &= nbsp;       (new.my_table_id, 

         &= nbsp;       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 =3D 'new a contents', b =3D = 'new b contents', c  =3D 'new c contents' where my_table_id =3D = 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

         &= nbsp;   (my_table_id, 

         &= nbsp;  c) 

VALUES 

         &= nbsp;   (new.my_table_id, 

         &= nbsp;   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

 

------=_NextPart_000_0016_01C89E2D.B4D5B340--