Received: from ns.krot.org (ns.krot.org [193.212.103.165]) by postgresql.org (8.11.3/8.11.4) with SMTP id f9KNxOP62316 for ; Sat, 20 Oct 2001 19:59:24 -0400 (EDT) (envelope-from postgresql@envisity.com) Received: (qmail 17316 invoked by uid 67); 20 Oct 2001 23:57:12 -0000 Message-ID: <20011020235712.24765.qmail@ns.krot.org> Content-Type: text/plain; charset=ISO-8859-1 Content-Disposition: inline MIME-Version: 1.0 From: "Aasmund Midttun Godal" To: pgsql-sql@postgresql.org Subject: CREATE RULE ON UPDATE/DELETE Date: Sat, 20 Oct 2001 23:57:12 GMT X-Mailer: Googley-Moogley Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id f9L00hM63380 X-Archive-Number: 200110/291 X-Sequence-Number: 5244 Can a rule see the where statement in a query which it has been triggered by? or is it simply ignored?? what happens? i.e. CREATE TABLE foo ( id INTEGER PRIMARY KEY, name TEXT ); CREATE VIEW bar AS SELECT * FROM foo; -- Great view? CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET id = NEW.id, name = NEW.name WHERE OLD.id = id; Now if I do a: UPDATE bar SET id = id + 10, WHERE id > 10; What really happens? Does the update first select from bar, and pick out which rows to do the update on, and then do the update on these rows or what? I tried it, and I got an answer I cannot explain, first it works, then it doesn't: envisity=# CREATE TABLE foo ( envisity(# id INTEGER PRIMARY KEY, envisity(# name TEXT envisity(# ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for tabl e 'foo' CREATE envisity=# envisity=# CREATE VIEW bar AS SELECT * FROM foo; -- Great view? CREATE envisity=# envisity=# CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET foo.id = NEW.id, foo.name = NEW.name WHERE OLD.id = foo.id; ERROR: parser: parse error at or near "." envisity=# CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE envisity=# INSERT INTO foo (1, 't'); ERROR: parser: parse error at or near "1" envisity=# INSERT INTO foo VALUES(1, 't'); INSERT 57054 1 envisity=# INSERT INTO foo VALUES(2, 'tr'); INSERT 57055 1 envisity=# INSERT INTO foo VALUES(12, 'tg'); INSERT 57056 1 envisity=# INSERT INTO foo VALUES(15, 'tgh'); INSERT 57057 1 envisity=# INSERT INTO foo VALUES(14, 'th'); INSERT 57058 1 envisity=# UPDATE bar SET id = id + 10 > envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 3 -- Here it works envisity=# select * from bar; id | name ----+------ 1 | t 2 | tr 22 | tg 24 | th 25 | tgh (5 rows) envisity=# #CREATE VIEW bar AS SELECT * FROM foo; -- Great view? ERROR: parser: parse error at or near "#" envisity=# DROP VIEW bar; DROP envisity=# CREATE VIEW bar AS SELECT id * 2 as id, name FROM foo; -- Great view ? CREATE envisity=# CREATE RULE bar_update AS ON UPDATE TO bar DO INSTEAD UPDATE foo SET id = NEW.id, name = NEW.name WHERE OLD.id = id; CREATE envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 0 envisity=# select * from bar; id | name ----+------ 2 | t 4 | tr 44 | tg 48 | th 50 | tgh (5 rows) envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 0 envisity=# select * from foo; id | name ----+------ 1 | t 2 | tr 22 | tg 24 | th 25 | tgh (5 rows) envisity=# UPDATE bar SET id = id + 10 where id > 10; UPDATE 0 -- Here it doesn't work. Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46