Re: question on update/delete rules on views

----- Original Message -----
From: Kyle Bateman <kyle(at)actarg(dot)com>
To: Brook Milligan <brook(at)biology(dot)nmsu(dot)edu>
Cc: <pgsql-sql(at)postgresql(dot)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