From: | Richard Broersma <richard(dot)broersma(at)gmail(dot)com> |
---|---|
To: | Seb <spluque(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Fwd: conditional rule not applied |
Date: | 2010-01-06 21:01:02 |
Message-ID: | 396486431001061301m1fa305fav211a98bb3b9f62f1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On Wed, Jan 6, 2010 at 12:40 PM, Seb <spluque(at)gmail(dot)com> wrote:
> I'm trying to create a rule to be applied on update to a view that
> consists of two joined tables. Table 'shoes' below is left-joined with
> table 'shoelaces' in the view 'footwear'. I'd like to create a simple
> update rule on the view, only if the value of a common column
> corresponds to an inexistent record in 'shoelaces', so the result is an
> INSERT into 'shoelaces' with the new record:
A couple of year's ago, I was seriously looking into update-able
views. But from my experience, I'm sorry to say you not going to find
a robust solution to this problem. There are at least three problems
with joined table update-able views:
1) You can only issue insert-update-delete statements that will only
affect one row.
2) You cannot serialize the update of a view's virtual row like you
can with a table's row. This allow leave the possibility of
concurrent update anomalies.
3) Application frameworks that use optimistic locking or use the
updated row count for validation will complain (and automatically
roll-back your work) when you attempt to perform an update.
The official use for update-able views is for limiting the results
from a *single* base table.
Having said all of this, it is possible to do what your describing.
I've seen Keith Larson make update-able views from a composite of
selected UNION and FULL OUT JOIN queries. But his solution was
extremely hackish.
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
From | Date | Subject | |
---|---|---|---|
Next Message | Seb | 2010-01-06 21:31:10 | Re: Fwd: conditional rule not applied |
Previous Message | Seb | 2010-01-06 20:40:46 | Fwd: conditional rule not applied |