Newbie question on RULEs .. or .. bug ?
Hello,
I have been working with a great database system called PostgreSQL for
many years ;-) but never had to use any RULEs. I now have to use and
update through a view and have written a few rules to make this possible
as per the manual.
My insert rules seems to work fine, but I can't make the update rule do
what I want and as I believe stated in the manual.
In the attached schema I have 2 basic tables and a view combining
those. I have 2 rules for insert on the view and these works fine. The
update rule, however, doesn't do what I want. The manual states that the
origsal query tree (where) is added to the rule qualification,
so I would exspect the update statement below would only update 1 row of
the ganntinfo table, but it updates all 5 ?
What am I doing wrong ?
projtaskdb=# SELECT * FROM ganntinfo ;
id | seq | category | name
-----+-----+----------+---------
46 | 0 | SRC | 2WWE
172 | 0 | SRC | RKD60
138 | 0 | SRC | Diverse
34 | 0 | SRC | VF
87 | 0 | SRC | 2WWE
(5 rows)
projtaskdb=# UPDATE tasks SET id = 87, category = 'SRC', name = '2WWE',
customer = 'Custm', description = 'test' WHERE id = 87 and category =
'SRC';
UPDATE 5
projtaskdb=# SELECT * FROM ganntinfo ;
id | seq | category | name
----+-----+----------+------
87 | 0 | SRC | 2WWE
87 | 0 | SRC | 2WWE
87 | 0 | SRC | 2WWE
87 | 0 | SRC | 2WWE
87 | 0 | SRC | 2WWE
(5 rows)
projtaskdb=#
I tried to put an 'explain' in front of the update within the update
rule, but got a syntax error. Why is that ?
Please help,
Leif
Attachments:
projtaskdb-test.schtext/plain; charset=US-ASCII; name=projtaskdb-test.schDownload
Leif Jensen <leif@crysberg.dk> writes:
CREATE RULE update_tasks2taskshead AS
ON UPDATE TO tasks WHERE NEW.seq = 0
DO NOTHING
;
That rule looks a bit useless ...
CREATE RULE update_tasks2ganntinfo AS
ON UPDATE TO tasks
DO INSTEAD (
update ganntinfo set
id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name
-- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category
;
)
;
You definitely need a WHERE clause in that rule; otherwise you get
exactly the result you saw: all rows of ganntinfo are updated. The
comment in the manual about the original WHERE clause really means
that the values of "NEW" will be constrained to take on only the
values determined by the original WHERE. Your update is basically a join
of ganntinfo with the subset of the tasks view determined by the
original WHERE --- so you have to constrain ganntinfo too. I suppose
that you want something like
update ganntinfo set
category = NEW.category, name = NEW.name
WHERE id = NEW.id AND seq = NEW.seq
;
since id/seq is your primary key for ganntinfo.
regards, tom lane
Hello Tom,
Thank you for the enlightment, I think I understand what you say.
There are however a few things I'm not sure about still. The update
seems to work as I would expect when I include one or more in there where
clause from the primary key. If I have a field not in the primary key
included in the where, I don't get anything updated, e.g.
update tasks set seq = 2 where id = 87 and name = '2WWE';
does nothing !??
Two questions:
1) Is there any way to define a view like this where I can insert and
update without these rules, just as if it was one real table ?
2) If I need to use rules to do update/insert on tasks, how can I make it
'transparent' as in the above example (the update that does nothing) ?
Greetings,
Leif
On Tue, 17 May 2005, Tom Lane wrote:
Leif Jensen <leif@crysberg.dk> writes:
CREATE RULE update_tasks2taskshead AS
ON UPDATE TO tasks WHERE NEW.seq = 0
DO NOTHING
;That rule looks a bit useless ...
Yeah, just disabled for now ;-)
Show quoted text
CREATE RULE update_tasks2ganntinfo AS
ON UPDATE TO tasks
DO INSTEAD (
update ganntinfo set
id = NEW.id, seq = NEW.seq, category = NEW.category, name = NEW.name
-- WHERE id = NEW.id AND seq = NEW.seq AND category = NEW.category
;
)
;You definitely need a WHERE clause in that rule; otherwise you get
exactly the result you saw: all rows of ganntinfo are updated. The
comment in the manual about the original WHERE clause really means
that the values of "NEW" will be constrained to take on only the
values determined by the original WHERE. Your update is basically a join
of ganntinfo with the subset of the tasks view determined by the
original WHERE --- so you have to constrain ganntinfo too. I suppose
that you want something likeupdate ganntinfo set
category = NEW.category, name = NEW.name
WHERE id = NEW.id AND seq = NEW.seq
;since id/seq is your primary key for ganntinfo.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings