Skip site navigation (1) Skip section navigation (2)

CREATE RULE ON UPDATE/DELETE

From: "Aasmund Midttun Godal" <postgresql(at)envisity(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: CREATE RULE ON UPDATE/DELETE
Date: 2001-10-20 23:57:12
Message-ID: 20011020235712.24765.qmail@ns.krot.org (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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 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(at)godal(dot)com - http://www.godal.com/
+47 40 45 20 46

Responses

pgsql-hackers by date

Next:From: Rod TaylorDate: 2001-10-21 01:28:25
Subject: Re: Package support for Postgres
Previous:From: Bill StudenmundDate: 2001-10-20 18:24:59
Subject: Re: schema support, was Package support for Postgres

pgsql-sql by date

Next:From: Douglas Rafael da SilvaDate: 2001-10-21 00:11:11
Subject: Re: Diferent databases on same query...
Previous:From: Esteban Gutierrez AbarzuaDate: 2001-10-20 18:05:44
Subject: Re: GROUPING

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group