Re: Re: Updating views

On Tue, Jun 05, 2001 at 01:17:00AM +0200, Rasmus Resen Amossen wrote:
> >  Problem is not 'where'. Views in Postgresql doesn't allows you insert,
> >  update or delete unless you define especila rules that explain Postgresql
> >  what to do in each case.
> >  Look Postgresql programming manual. You can see a few examples of rules in
> >  views.
>
> OK, but I can't see how to make a single rule that allows me to update
> an arbitray set of attributes from an arbitray where-clause.
>
> Example:
> I have a table named 'extable(a,b,c,d)' and a view 'exview(b,c,d)' for
> this table. How can I with a single rule allow the following updates:
>   update exview set b=10, c=0 where d=11;
>   update exview set b=0 where c > d;
>   update exview set d=123 where b=c and c=d;

something like...

    create rule exmunge as
        on update to exview
        do instead (
            update extable
            set
                -- stamp = current_timestamp,
                -- something = old.fldA + new.fldB,
                b = new.b,
                c = new.c,
                d = new.d
        );

then, try

    update exview
        set b = 10, c = 0
        where d = 11;
    update exview
        set b = 0
        where c > d;
    update exview
        set d = 123
        where b = c and c = d;

is that what you're after?

--
#95: We are waking up and linking to each other. We are watching. But
we are not waiting.  -- www.cluetrain.com

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!