From: Richard Huxton <dev@archonet.com>
To: Kyle Bateman <kyle@actarg.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: question on update/delete rules on views
Date: Wed, 17 May 2000 20:45:32 +0100
Message-ID: <007f01bfc038$7842ab80$1001a8c0@archonet.com> (raw)
References: <3921E506.82424CAF@actarg.com>
<200005171352.HAA19867@biology.nmsu.edu>
<3922B7A2.5BEF5F5E@actarg.com>
----- 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, kyle@actarg.com
Subject: Re: question on update/delete rules on views
In-Reply-To: <007f01bfc038$7842ab80$1001a8c0@archonet.com>
References: <3921E506.82424CAF@actarg.com> <200005171352.HAA19867@biology.nmsu.edu> <3922B7A2.5BEF5F5E@actarg.com> <007f01bfc038$7842ab80$1001a8c0@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox