Re: question on update/delete rules on views

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