Re: CREATE RULE ON UPDATE/DELETE

On Sat, 20 Oct 2001, Joel Burton wrote:

> On Sat, 20 Oct 2001, Aasmund Midttun Godal wrote:
>
> > Can a rule see the where statement in a query which it has been
> > triggered by? or is it simply ignored?? what happens?
> >
>
> Looking over your question, I wanted to clarify the problem a bit, so:
> (cleaned up example a bit from Aasmund)

> drop view normal;
> drop view dbl;
> drop table raw;
>
> CREATE TABLE raw (id INT PRIMARY KEY, name TEXT );
> INSERT INTO raw VALUES(1, 'a');
> INSERT INTO raw VALUES(2, 'b');
> INSERT INTO raw VALUES(12, 'c');
> INSERT INTO raw VALUES(15, 'd');
> INSERT INTO raw VALUES(14, 'e');
>
>
> -- set up two views: "normal", a simple view,
> -- and "dbl", which shows id * 2
>
> -- create basic rules to allow update to both views
>
> CREATE VIEW normal AS SELECT * FROM raw;
>
> CREATE RULE normal_update AS ON UPDATE TO normal DO INSTEAD UPDATE raw SET
> id = NEW.id, name = NEW.name WHERE OLD.id = id;
>
> CREATE VIEW dbl AS SELECT id * 2 as id, name FROM raw;
>
> CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET
> id = NEW.id, name = NEW.name WHERE OLD.id = id;

> The issue is that there are no IDs over 10 that have another ID that is
> exactly their value, so the first update to "dbl" does nothing.
>
> The second time, w/o the ID>10 restriction, it finds 1(a), and double
> that, 2(b), and adds 10; getting confused about which record to edit.
>
> Is this the best way to interpret this? Is this a bug?

Don't think so. I think the rule doesn't make any sense.
NEW.id and OLD.id are probably dbl values, so saying OLD.id=id (where id
is raw.id since that's the update table) isn't correct. It probably
should be OLD.id=id*2 (which seems to work for me, btw) It's editing
a different row than the one that's being selected.