Re: feature request for Postgresql Rule system.

On Mon, 2006-12-18 at 15:30 -0800, Richard Broersma Jr wrote:
> > > 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);
>

Actually, I am seeing some unexpected behavior, or rather behavior that
I wouldn't expect. After the first UPDATE in the rule, NEW and OLD are
gone.

After reading up on the rules document, I think this is happening
because the WHERE clause is applied again, and since neither NEW nor OLD
satisfy the WHERE clause (because of the first UPDATE), they become non-
existent.

I am still a little unsure on this topic, would someone else like to
comment? It seems almost like NEW and OLD act like views with the outer
WHERE clause included, rather than materialized relations.

The way to fix this definitively is to pass the OLD/NEW values as
arguments to a function, and then they are stored as new values until
the end of the function's execution, during which you can run as many
UPDATEs as you want.

Regards,
Jeff Davis