Re: Creating multiple Rules for on update

Richard Broersma Jr <rabroersma(at)yahoo(dot)com> writes:

> I have a view joining two tables with a (1 to 1) relationship. I am trying
> to create two update rules (1 rule for each table in the view). To
> accomplish this I am trying (unsuccessfully) to use the where condition
> syntax of the update rules.
>
> Basically, I only want to update a table if the columns related to its tuple
> are altered. If possible, I don't want to update/touch a tuple from a table
> who's data remains unchanged. However, the where conditions that I am using
> to make this distinction are giving the following error:
>
> "ERROR: cannot update a view"
> "HINT: You need an unconditional ON UPDATE DO INSTEAD rule."
>
> Ofcourse, if my understanding of the use of the rule's WHERE condition is
> why off base, I would be enteresting in knowing the proper way it should be
> used.

When I read the docs about RULEs I remember seeing that an unqualified RULE
was needed otherwise PG wouldn't know that the operation was complete and
would fail.

I haven't used them, but I believe that you have two options:

- use a RULE with your filtering conditions AND add an unqualified RULE that
is always run

- use an unqualified RULE and call a function that will perform the action
for you on the right tables

I believe the second solution leads to cleaner code and is easier to update /
debug. But I haven't tried it...

--
Jorge Godoy <jgodoy(at)gmail(dot)com>