On Fri, Jun 27, 2008 at 3:06 AM, Dean Rasheed <dean_rasheed@hotmail.com> wrote:
>> Someone pointed out in an earlier thread that a
>> way to fix this, for updates on a multi-table view (where most of the
>> complication lies), is to write a "trigger" function that updates all
>> the constituent tables except for one, and then write a rule that
>> calls that function and then updates the one remaining table itself.
>> This seems to work okay although I have not tested it with many
>> clients.
>
> Yes that would seem to work. For UPDATE anyway. Although if it were purely
> DML that you were doing, you would probably be better off just having multiple
> UPDATE statements in the rule body. Then they would stand a better chance
> of being rewritten and executed more efficiently.
As Richard Broersma pointed out in the earlier thread, this approach
has a tendency to result in "partial updates" if the WHERE clause in
the UPDATE statement issued on the view includes more than just the
primary key.
http://archives.postgresql.org/pgsql-general/2008-06/msg00479.php
http://archives.postgresql.org/pgsql-general/2006-12/msg01048.php
This is probably another one of the "subtle pitfalls" you mentioned,
but to me it means that using multiple UPDATE statements in the rule
body is unacceptable.
Mike
