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 <[EMAIL PROTECTED]> 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 ;-) > > > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
