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(at)lfix(dot)co(dot)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