|
| 1 | +From pgsql-general-owner+M10387@postgresql.org Mon Jun 4 22:02:55 2001 |
| 2 | +Return-path: <pgsql-general-owner+M10387@postgresql.org> |
| 3 | +Received: from postgresql.org (webmail.postgresql.org [216.126.85.28]) |
| 4 | +by candle.pha.pa.us (8.10.1/8.10.1) with ESMTP id f5522tc28169 |
| 5 | +for <pgman@candle.pha.pa.us>; Mon, 4 Jun 2001 22:02:55 -0400 (EDT) |
| 6 | +Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) |
| 7 | +by postgresql.org (8.11.3/8.11.1) with SMTP id f5520BE14492; |
| 8 | +Mon, 4 Jun 2001 22:00:11 -0400 (EDT) |
| 9 | +(envelope-from pgsql-general-owner+M10387@postgresql.org) |
| 10 | +Received: from sss.pgh.pa.us ([192.204.191.242]) |
| 11 | +by postgresql.org (8.11.3/8.11.1) with ESMTP id f551hHE09364 |
| 12 | +for <pgsql-general@postgresql.org>; Mon, 4 Jun 2001 21:43:17 -0400 (EDT) |
| 13 | +(envelope-from tgl@sss.pgh.pa.us) |
| 14 | +Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) |
| 15 | +by sss.pgh.pa.us (8.11.3/8.11.3) with ESMTP id f551gwR09928; |
| 16 | +Mon, 4 Jun 2001 21:42:58 -0400 (EDT) |
| 17 | +To: Rasmus Resen Amossen <spunk@rhk.dk> |
| 18 | +cc: pgsql-general@postgresql.org |
| 19 | +Subject: Re: [GENERAL] Re: Updating views |
| 20 | +In-Reply-To: <3B1C16EC.8D9FB57B@rhk.dk> |
| 21 | +References: <20010605001048.A2133@lorien.net> <3B1C16EC.8D9FB57B@rhk.dk> |
| 22 | +Comments: In-reply-to Rasmus Resen Amossen <spunk@rhk.dk> |
| 23 | +message dated "Tue, 05 Jun 2001 01:17:00 +0200" |
| 24 | +Date: Mon, 04 Jun 2001 21:42:57 -0400 |
| 25 | +Message-ID: <9925.991705377@sss.pgh.pa.us> |
| 26 | +From: Tom Lane <tgl@sss.pgh.pa.us> |
| 27 | +Precedence: bulk |
| 28 | +Sender: pgsql-general-owner@postgresql.org |
| 29 | +Status: OR |
| 30 | + |
| 31 | +Rasmus Resen Amossen <spunk@rhk.dk> writes: |
| 32 | +> OK, but I can't see how to make a single rule that allows me to update |
| 33 | +> an arbitray set of attributes from an arbitray where-clause. |
| 34 | + |
| 35 | +The reason the system doesn't do that for you is that it's *hard* to |
| 36 | +figure out what to do for an arbitrary where-clause. An automatic rule |
| 37 | +has no chance of doing the right thing, because the right thing depends |
| 38 | +on what you intend. For example, if your view has |
| 39 | +select ... where a>5; |
| 40 | +what do you think ought to happen if someone tries to insert a row |
| 41 | +with a<5? Is that an error? A no-op? Does the row go in anyway, |
| 42 | +you just can't see it in the view? Does the row go into some other |
| 43 | +table instead? Is it OK to change the A column at all? It all depends |
| 44 | +on the semantics of your database design. So you have to figure out |
| 45 | +what you want and write rules that do it. |
| 46 | + |
| 47 | +The mechanics of the rule are not that painful once you've decided what |
| 48 | +the reverse mapping from inserted/updated data to underlying tables |
| 49 | +ought to be. One thing that may help is to realize that you don't need |
| 50 | +a separate rule for each combination of set of attributes that might be |
| 51 | +updated. "new.*" is defined for all columns including the ones that |
| 52 | +didn't change, so you can just do something like |
| 53 | + |
| 54 | +update ... set f1 = new.f1, f2 = new.f2, ... |
| 55 | + |
| 56 | +without worrying about just which columns the user tried to update. |
| 57 | +Likewise, the where clause in the user's query is not yours to worry |
| 58 | +about; that condition gets added onto the stuff in your rule. |
| 59 | + |
| 60 | +> In other words: I want to make the update of 'exview' transparent to |
| 61 | +> 'extable'. |
| 62 | + |
| 63 | +If it's really transparent, one wonders why you bothered with a view |
| 64 | +at all. Useful views tend to be nontrivial mappings of the underlying |
| 65 | +data, which is why it's nontrivial to figure out what the reverse |
| 66 | +mapping ought to be. |
| 67 | + |
| 68 | +regards, tom lane |
| 69 | + |
| 70 | +---------------------------(end of broadcast)--------------------------- |
| 71 | +TIP 6: Have you searched our list archives? |
| 72 | + |
| 73 | +http://www.postgresql.org/search.mpl |
| 74 | + |