----- Original Message -----
From: Kyle Bateman <kyle@actarg.com>
To: Brook Milligan <brook@biology.nmsu.edu>
Cc: <pgsql-sql@postgresql.org>
Sent: Wednesday, May 17, 2000 4:15 PM
Subject: Re: [SQL] question on update/delete rules on views
> Brook Milligan wrote:
>
> > create rule view_a_r_update as on update to view_a
> > do instead
> > update a set two = new.two;
> >
> > The problem is that your INSTEAD UPDATE rule is not constrained in any
> > way; it DOES hit every row. Instead, do something like:
> >
> > create rule view_a_r_update as on update to view_a
> > do instead
> > update a set two = new.two
> > where id = old.id;
> >
> > where id is a primary key in your table.
> >
>
> Thanks for the help. The problem with your suggestion is the view has to
> anticipate which column(s) the calling query wants to look at. What if
> the calling query has not specified the primary key in its where clause?
> In our real case, the table has many columns. There are a variety of
> queries that act on the table based on a variety of conditions in a
> variety of columns. I'd like to avoid having to have a separate rule or
> view for every possible where combination. Maybe that is not possible,
> but the manual seems to say it should work, so that's why I'm asking the
> question.
>
AFAIK it doesn't matter if the original query used a field - the "old" and
"new" in the rule represent the row being updated (before and after) - you
can access any column.
-- Richard Huxton