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
[EMAIL PROTECTED] - http://www.godal.com/
+47 40 45 20 46
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
