incremental-aggregate-update

incremental.sql

drop table if exists ofec_sched_a_aggregate_zip;
create table ofec_sched_a_aggregate_zip as
select
    cmte_id,
    rpt_yr + rpt_yr % 2 as cycle,
    contbr_zip as zip,
    sum(contb_receipt_amt) as total
from sched_a
    where rpt_yr >= 2011
    group by cmte_id, cycle, zip
;

create index on ofec_sched_a_aggregate_zip (cmte_id);
create index on ofec_sched_a_aggregate_zip (cycle);
create index on ofec_sched_a_aggregate_zip (zip);

create or replace function ofec_sched_a_zip_update() returns trigger as $$
begin
    if tg_op = 'INSERT' then
        with vnew (cmte_id, cycle, zip, amount) as (
            values (new.cmte_id, new.rpt_yr + new.rpt_yr % 2, new.contbr_zip, new.contb_receipt_amt)
        ),
        vold (cmte_id, cycle, zip, amount) as (
            values (old.cmte_id, old.rpt_yr + old.rpt_yr % 2, old.contbr_zip, old.contb_receipt_amt)
        ),
        upsert as (
            update ofec_sched_a_aggregate_zip ag
            set total = total + vnew.amount
            from vnew
            where (ag.cmte_id, ag.cycle, ag.zip) = (vnew.cmte_id, vnew.cycle, vnew.zip)
            RETURNING ag.*
        )
        insert into ofec_sched_a_aggregate_zip
            (cmte_id, cycle, zip, total)
        select cmte_id, cycle, zip, amount
        from vnew
        where not exists (
            select 1
            from upsert up
            where (up.cmte_id, up.cycle, up.zip) = (vnew.cmte_id, vnew.cycle, vnew.zip)
        )
        ;
        return new;
    elsif tg_op = 'UPDATE' then
        if new.contb_receipt_amt != old.contb_receipt_amt then
            update ofec_sched_a_aggregate_zip ag
            set total = total + new.contb_receipt_amt - old.contb_receipt_amt
            where (ag.cmte_id, ag.cycle, ag.zip) = (vnew.cmte_id, vnew.cycle, vnew)
            ;
        end if;
        return new;
    elsif tg_op = 'DELETE' then
        update ofec_sched_a_aggregate_zip ag
        set total = total - old.contb_receipt_amt
        where (ag.cmte_id, ag.cycle, ag.zip) = (vold.cmte_id, vold.cycle, vold.zip)
        ;
        return old;
    end if;
end
$$ language plpgsql;

drop trigger if exists ofec_sched_a_zip_trigger on sched_a;
create trigger ofec_sched_a_zip_trigger before insert or update or delete
    on sched_a for each row execute procedure ofec_sched_a_zip_update()
;