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@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 ;-)

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