> > postgres=# update vwife
> > set name = 'Katheryn',
> > dresssize = 12
> > where (id,name,dresssize)=(2,'katie',11);
>
> In "UPDATE #", # is the result of the libpq function PQcmdTuples(), and
> it refers to the number of tuples affected by the last command executed.
> What's happening is that the first UPDATE in the rule changes 1 record
> in public.person, but the second update matches no rows, so that value
> is 0.
agreed.
> That means that the WHERE clause of the second update matches nothing.
> Are you perhaps using two different id fields, and comparing against the
> wrong one?
In this case, the id are that same since wife.id is a foreign key of person.id. The think the
problem lies in the where clause of the update statement to the update-able view.
where (id,name,dresssize)=(2,'katie',11);
If I only specify "where id=2" in the update statement to the view everything works fine and the
updates always succeed. However, for some reason, the rule system also takes into account the
other redundant fields in the where clause even though I have no such fields defined in the rule's
update statements. In this case (name,dresssize)=('katie',11), causes the update to partially
succeed and partially fail because the initial update will find ('katie',11) but the second
doesn't since one of these values is already changed due to the first update statement in the
rule.
It would be nice if the rule system could either ignore redundant fields in the where clause from
update statements made to an update-able view; Or if all update statements in the update rule
could still see the initial state of the view's tuple so that all statements in the rule can
successfully find a match in the rule statements' where clauses. <I hope this last sentence is
clear.>
Regards,
Richard Broersma Jr.