On Wed, Jan 6, 2010 at 12:40 PM, Seb <splu...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
