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

Sat, 20 Oct 2001 23:57:12 GMT
"Aasmund Midttun Godal" <postgresql@envisity.com>
CREATE RULE ON UPDATE/DELETE
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)


-- set up tables

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;


-- now test this

UPDATE normal SET id = id + 10 where id > 10;  -- works fine

UPDATE dbl SET id = id + 10 where id > 10;    -- above shows UPDATE 0
                                              -- even though there are ids > 10

UPDATE dbl SET id = id + 10;                  -- UPDATE 1; shows table
SELECT * FROM dbl;                            -- inconsistencies: two "a"s
SELECT * FROM raw;



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?


-- 

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant


Sat, 20 Oct 2001 23:31:10 -0400 (EDT)
Joel Burton <joel@joelburton.com>
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.



Sun, 21 Oct 2001 00:41:29 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: CREATE RULE ON UPDATE/DELETE

> 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.

I forgot to mention in this that I needed to made an additional change in
the rule to make the ids come out correct at the end :(.  The update set
id=NEW.id should be id=NEW.id/2 of course...  Otherwise the +10 becomes a
+20.



Sun, 21 Oct 2001 01:33:59 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: CREATE RULE ON UPDATE/DELETE
Joel Burton <joel@joelburton.com> writes:
> 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;

Surely you'd need something like

CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET
id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2;

(untested...)

			regards, tom lane

Sun, 21 Oct 2001 12:47:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: CREATE RULE ON UPDATE/DELETE 
Yes, I agree perfectly... I never thought of that! I would really like it if some more info was added to the docs regarding info on rules and triggers. The section on update rules is quite good, but some more would never hurt. One point in the trigger vs rules section which at least to me is very important is the simple fact that you cannot have a trigger on a select... Ok I understand why - but it took some time...

Thank you for answering my questions!

regards,

Aasmund.
On Sun, 21 Oct 2001 12:47:41 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joel Burton <joel@joelburton.com> writes:
> 
> 
> Surely you'd need something like
> 
> CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET
> id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2;
> 
> (untested...)
> 
> 			regards, tom lane

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

Sun, 21 Oct 2001 22:26:03 GMT
"Aasmund Midttun Godal" <postgresql@envisity.com>
Re: CREATE RULE ON UPDATE/DELETE
Yes, I agree perfectly... I never thought of that! I would really like it if some more info was added to the docs regarding info on rules and triggers. The section on update rules is quite good, but some more would never hurt. One point in the trigger vs rules section which at least to me is very important is the simple fact that you cannot have a trigger on a select... Ok I understand why - but it took some time...

Thank you for answering my questions!

regards,

Aasmund.
On Sun, 21 Oct 2001 12:47:41 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Joel Burton <joel@joelburton.com> writes:
> 
> 
> Surely you'd need something like
> 
> CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET
> id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2;
> 
> (untested...)
> 
> 			regards, tom lane

Aasmund Midttun Godal

aasmund@godal.com - http://www.godal.com/
+47 40 45 20 46

Sun, 21 Oct 2001 22:26:03 GMT
"Aasmund Midttun Godal" <postgresql@envisity.com>
Re: CREATE RULE ON UPDATE/DELETE

I have added the following text to the CREATE TRIGGER manual page to
address this issue.  It often confuses people so it is good to point
out:

  <para>
  <command>SELECT</command> does not modify any rows so you can not
  create <command>SELECT</command> triggers.
  </para>


---------------------------------------------------------------------------

> Yes, I agree perfectly... I never thought of that! I would really like it if some more info was added to the docs regarding info on rules and triggers. The section on update rules is quite good, but some more would never hurt. One point in the trigger vs rules section which at least to me is very important is the simple fact that you cannot have a trigger on a select... Ok I understand why - but it took some time...
> 
> Thank you for answering my questions!
> 
> regards,
> 
> Aasmund.
> On Sun, 21 Oct 2001 12:47:41 -0400, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Joel Burton <joel@joelburton.com> writes:
> > 
> > 
> > Surely you'd need something like
> > 
> > CREATE RULE dbl_update AS ON UPDATE TO dbl DO INSTEAD UPDATE raw SET
> > id = NEW.id / 2, name = NEW.name WHERE OLD.id = id * 2;
> > 
> > (untested...)
> > 
> > 			regards, tom lane
> 
> Aasmund Midttun Godal
> 
> aasmund@godal.com - http://www.godal.com/
> +47 40 45 20 46
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 29 Nov 2001 17:17:39 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: CREATE RULE ON UPDATE/DELETE