pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (8+ messages)  latest in thread

Message-ID: <007f01bfc038$7842ab80$1001a8c0@archonet.com>
Permalink:  ../007f01bfc038$7842ab80$1001a8c0@archonet.com/
Also on:    postgresql.org/message-id/007f01bfc038$7842ab80$1001a8c0@archonet.com

 · 

reply

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