CREATE RULE ON UPDATE/DELETE

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(at)godal(dot)com - http://www.godal.com/
+47 40 45 20 46