CREATE RULE ON UPDATE/DELETE - Mailing list pgsql-sql

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
rowsor 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 | tr22 | tg24 | th25 | 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 | tr44 | tg48 | th50 | tgh
(5 rows)

envisity=# UPDATE bar SET id = id + 10 where id > 10;
UPDATE 0
envisity=# select * from foo;id | name 
----+------ 1 | t 2 | tr22 | tg24 | th25 | 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