DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type:content-transfer-encoding;
	b=JAGWyzsUCVOyTWrr5tQSKSxndBUhC+rHRY+c9nKo5qXYKbisZLpA7iFXzOYX6fxxQr
	yCteXsKqx5V0aXYH+SvXfVEIQM+Q3H9GDXDnjl4KQ69kY4aItINU1wU9W45a4BWylieF
	yAh3mwQMxCwxtPLPX3nAAujC0IUkV7drrXIPw=
MIME-Version: 1.0
In-Reply-To: <87fx6jm0fl.fsf@kolob.sebmags.homelinux.org>
References: <87fx6jm0fl.fsf@kolob.sebmags.homelinux.org>
Date: Wed, 6 Jan 2010 13:01:02 -0800
Message-ID: <396486431001061301m1fa305fav211a98bb3b9f62f1@mail.gmail.com>
Subject: Re: Fwd: conditional rule not applied
From: Richard Broersma <richard.broersma@gmail.com>
To: Seb <spluque@gmail.com>
Cc: pgsql-sql@postgresql.org
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

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. =A0Table 'shoes' below is left-joined with
> table 'shoelaces' in the view 'footwear'. =A0I'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.

--=20
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug