Re: Newbie question on RULEs .. or .. bug ?

Leif Jensen <leif(at)crysberg(dot)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