From: Richard Broersma <richard.broersma@gmail.com>
To: Seb <spluque@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Fwd: conditional rule not applied
Date: Wed, 6 Jan 2010 13:01:02 -0800
Message-ID: <396486431001061301m1fa305fav211a98bb3b9f62f1@mail.gmail.com> (raw)
In-Reply-To: <87fx6jm0fl.fsf@kolob.sebmags.homelinux.org>
References: <87fx6jm0fl.fsf@kolob.sebmags.homelinux.org>
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: richard.broersma@gmail.com, spluque@gmail.com
Subject: Re: Fwd: conditional rule not applied
In-Reply-To: <396486431001061301m1fa305fav211a98bb3b9f62f1@mail.gmail.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