Skip site navigation (1) Skip section navigation (2)

Re: question on update/delete rules on views

From: Brook Milligan <brook(at)biology(dot)nmsu(dot)edu>
To: kyle(at)actarg(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: question on update/delete rules on views
Date: 2000-05-17 17:51:48
Message-ID: 200005171751.LAA20341@biology.nmsu.edu (view raw or flat)
Thread:
Lists: pgsql-sql
   >    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.

I think you misunderstand what is going on.  The original WHERE clause
(in your query) defines a set of tuples to which the UPDATE rule will
be applied.  In the example above, each of those tuples will have a
primary key value (old.id in that case) and the matching field(s) in
table (or view) a will be changed as dictated by the rule.  Thus, for
every tuple selected by your WHERE clause, the corresponding tuple in
the underlying table will be updated.  Note that as many fields as you
wish to allow updates on can be included in the set ... part of the
rule; any that are not different will just be changed to the same
value (i.e., there will be no effect).  Consequently, you don't need
lots of rules for every combination of columns (unless there are other
reasons to restrict the set of columns modifiable by different views).

Cheers,
Brook

In response to

Responses

pgsql-sql by date

Next:From: mikeoDate: 2000-05-17 18:43:40
Subject: remove line type?
Previous:From: mikeoDate: 2000-05-17 17:41:24
Subject: line type

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group