pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Aasmund Midttun Godal <postgresql@envisity.com>
To: pgsql-sql@postgresql.org
Subject: CREATE RULE ON UPDATE/DELETE
Date: Sat, 20 Oct 2001 23:57:12 GMT
Message-ID: <20011020235712.24765.qmail@ns.krot.org> (raw)

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



view thread (7+ messages)  latest in thread

Message-ID: <20011020235712.24765.qmail@ns.krot.org>
Permalink:  ../20011020235712.24765.qmail@ns.krot.org/
Also on:    postgresql.org/message-id/20011020235712.24765.qmail@ns.krot.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: postgresql@envisity.com
  Subject: Re: CREATE RULE ON UPDATE/DELETE
  In-Reply-To: <20011020235712.24765.qmail@ns.krot.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox