Re: Newbie question on RULEs .. or .. bug ? - Mailing list pgsql-general

From Leif Jensen
Subject Re: Newbie question on RULEs .. or .. bug ?
Date
Msg-id Pine.LNX.4.58.0505181617470.30428@samba.crysberg.dk
Whole thread Raw
In response to Re: Newbie question on RULEs .. or .. bug ?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
   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
>

pgsql-general by date:

Previous
From: Devrim GUNDUZ
Date:
Subject: 8.0.3 RPMs for PPC
Next
From: Scott Marlowe
Date:
Subject: Re: [JDBC] Storing/Using Passwords