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

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(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 ...

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
>