On Wed, Jan 6, 2010 at 12:40 PM, Seb <spluque@gmail.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
