Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 5BF752E0064 for ; Mon, 14 Apr 2008 21:34:03 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 73934-03 for ; Mon, 14 Apr 2008 21:33:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.152]) by developer.postgresql.org (Postfix) with ESMTP id 5ABA92E0045 for ; Mon, 14 Apr 2008 21:33:58 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 22so1758338fge.23 for ; Mon, 14 Apr 2008 17:33:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=CzC5J3s5ciGHmtYdkiVPkOGessXUsolmAkH8GU92dT4=; b=jkiIFJFnz0PphhVi0kCp1//g0Diwx0Tl3Zxz/unst2S9j1sctvlwpf/Twpp1zQRmCu1DBLDeJihx8FXDeUSAJmdEty46Nif7oGx83nsxu7pz5d43Q8ZPWbgo41a6a5NsKgrX+QLMIFtdf1EFrq2qnMFeo6pCtm6Hav3cQamFONI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=qpCLnFZ3QsHc+mYmug0H9T+Oc/AUg3wwZcd0eV18KfZTFkYIQ89NBbEFENjvSCStfePk8BYHxLPlX56gOvcBH7ITwBcNnWhRyFZrFsePz6iw7AeUIRG7KsuoBaIG/SseP3fa6TnFrlWmsMgUEMMYDtRNgL1jrceF0VnKXxttm+g= Received: by 10.82.139.20 with SMTP id m20mr10390850bud.16.1208219635841; Mon, 14 Apr 2008 17:33:55 -0700 (PDT) Received: by 10.82.112.18 with HTTP; Mon, 14 Apr 2008 17:33:55 -0700 (PDT) Message-ID: <36af4bed0804141733t14d01791t1b7499ed3b986484@mail.gmail.com> Date: Tue, 15 Apr 2008 06:03:55 +0530 From: "Robins Tharakan" To: "Chad Showalter" Subject: Re: rule for update view that updates/inserts into 2 tables Cc: pgsql-sql@postgresql.org In-Reply-To: <001501c89e4f$3be75340$b3b5f9c0$@net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_22136_17421385.1208219635842" References: <001501c89e4f$3be75340$b3b5f9c0$@net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/83 X-Sequence-Number: 30622 ------=_Part_22136_17421385.1208219635842 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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=3D1' the RUL= E now inserts another row with my_table_id =3D 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 =3D au.my_table_id AND au.audit_id =3D (SELECT max(audit_id) FROM my_audit_table WHERE au.my_table_id =3D 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 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 =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 > > a =3D new.a, > > b =3D new.b > > WHERE > > my_table_id =3D 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 t= o > 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 wil= l > be > > inserted with each call after the first call. > > > > Specifically, > > =B7 after the first call, 1 row is inserted > > =B7 after the second call, 2 rows are inserted > > =B7 after the third call, 4 rows are inserted > > =B7 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 us= e > a sp. > > > > Thanks, > > Chad > > > ------=_Part_22136_17421385.1208219635842 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi Chad,

(Pardon me if I am shooting the stars here...)

Don&#= 39;t you think that on each update, you would be creating a new row that sa= tisfies that very given condition for the view ?

By that I mean that= when you do a 'UPDATE... WHERE my_table_id=3D1' the RULE now inser= ts another row with my_table_id =3D 1... which now makes two rows that sati= sfy the criteria for the view.

The second time you run the update, the RULE inserts a row (in the my_a= udit_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 RU= LE 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 =3D au.my_table_id
  AND au.audit_id = =3D (SELECT max(audit_id) FROM my_audit_table WHERE au.my_table_id =3D my_a= udit_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... a= nd 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&#= 39;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 <c= showalter@bplglobal.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             &nb= sp;   

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 =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 

            =     a =3D new.a, 

            =     b =3D new.b  

WHERE 

            =     my_table_id =3D 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&#= 39;, '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,

=B7         after the first call, 1 row is inserted

=B7         after the second call, 2 rows are inserted

=B7         after the third call, 4 rows are inserted

=B7         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

 


------=_Part_22136_17421385.1208219635842--