Re: Can this pl/pgsql be simplified?

On Fri, 2005-11-25 at 12:19 -0800, CSN wrote:
> I have a trigger function that simply updates item counts when the items table changes (member_id
> or active changes). I'm curious if this bit of the code can be simplified? :)
>
> thanks
> csn
>
>
>
> ELSIF TG_OP = 'UPDATE' THEN
>
>   IF (OLD.member_id is NULL and NEW.member_id is not null) or (OLD.member_id is not NULL and
> NEW.member_id is null) or OLD.member_id <> NEW.member_id THEN
>     IF OLD.member_id is not null then
>       IF OLD.active is true then
>         update members set
>           items_submitted=items_submitted-1,
>           items_approved=items_approved-1
>           where id=OLD.member_id;
>       ELSE
>         update members set
>           items_submitted=items_submitted-1
>           where id=OLD.member_id;
>       END IF;
>     END IF;
>
>     IF NEW.member_id is not null then
>       IF NEW.active is true then
>         update members set
>           items_submitted=items_submitted+1,
>           items_approved=items_approved+1
>           where id=NEW.member_id;
>       ELSE
>         update members set
>           items_submitted=items_submitted+1
>           where id=NEW.member_id;
>       END IF;
>     END IF;
>   ELSIF OLD.active is false and NEW.active is true then
>           update members set
>                   items_approved=items_approved+1
>                   where id=NEW.member_id;
>   ELSIF OLD.active is true and NEW.active is false then
>           update members set
>                   items_approved=items_approved-1
>                   where id=NEW.member_id;
>   END IF;

I think this is logically equivalent:

    IF OLD.member IS DISTINCT FROM NEW.member then
        IF OLD.member_id is not null then
            update members set
               items_submitted=items_submitted-1,
               items_approved=items_approved-(CASE WHEN OLD.active THEN 1 ELSE 0 END)
               where id=OLD.member_id;
        END IF;
        IF NEW.member_id is not null then
            update members set
               items_submitted=items_submitted+1,
               items_approved=items_approved+(CASE WHEN NEW.active THEN 1 ELSE 0 END)
               where id=NEW.member_id;
        END IF;
    ELSIF OLD.active <> NEW.active then
            update members set
               items_approved=items_approved+(CASE WHEN NEW.active THEN 1 ELSE -1 END)
               where id=NEW.member_id;
    END IF;

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html