Re: feature request for Postgresql Rule system.

On Mon, 2006-12-18 at 17:09 -0800, Richard Broersma Jr wrote:
> > 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.
>
> I guess the end-result behaviour I am looking for (as you mentioned) is having an update-able view
> behave exactly as if it were a table in regard to update and delete statements.  (Delete
> statements had a similar behavior behaviour, but I got around that problem by using "delete
> cascade" on the leaf tables.)
>

Try:

CREATE OR REPLACE FUNCTION upd_func(INT,TEXT,INT) RETURNS VOID
  LANGUAGE sql AS
$upd_func$
  UPDATE public.person SET name = $2 WHERE id = $1;
  UPDATE public.wife SET dresssize = $3 WHERE id = $1;
$upd_func$;

CREATE OR REPLACE RULE vwife_update AS ON UPDATE TO public.vwife DO
INSTEAD SELECT upd_func(OLD.id,NEW.name,NEW.dresssize);

The difference here is that by passing the values into a function, it
creates a copy of the value, meaning it won't change due to an UPDATE.
The only negative of using a function is that the number of affected
tuples will always be zero.

Thanks for bringing this topic up... it made me understand the rule
system much better than I did before. I think I'll have to read through
that document a few more times.

Regards,
    Jeff Davis