idempiere trigger for summary

set SEARCH_PATH=adempiere



--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE ly_time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX ly_time_dimension_key ON ly_time_dimension(time_key);

CREATE TABLE ly_sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX ly_sales_fact_time ON ly_sales_fact(time_key);

--
-- Summary table - sales by time.
--
CREATE TABLE ly_sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX ly_sales_summary_bytime_key ON ly_sales_summary_bytime(time_key);

--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION ly_maint_sales_summary_bytime() RETURNS TRIGGER
AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN

            delta_time_key    = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold  = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- forbid updates that change the time_key -
            -- (probably not too onerous, as DELETE + INSERT is how most
            -- changes will be made).
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -> % not allowed',
                                                      OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key    = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold  = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key    = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold  = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


        -- Insert or update the summary row with the new values.
        <<insert_update>>
        LOOP
            UPDATE ly_sales_summary_bytime
                SET amount_sold = amount_sold + delta_amount_sold,
                    units_sold  = units_sold  + delta_units_sold,
                    amount_cost = amount_cost + delta_amount_cost
              WHERE time_key    = delta_time_key;

            EXIT insert_update WHEN found;

            BEGIN
                INSERT INTO ly_sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );

                EXIT insert_update;

            EXCEPTION
                WHEN UNIQUE_VIOLATION THEN
                    -- do nothing
            END;
        END LOOP insert_update;

        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER ly_maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON ly_sales_fact
    FOR EACH ROW EXECUTE PROCEDURE ly_maint_sales_summary_bytime();

select * from ly_sales_summary_bytime
1;10.00;3;15.00
--
1;30.00;8;50.00
--
1;30.00;8;50.00
2;40.00;15;135.00
--
1;30.00;8;50.00
2;50.00;16;148.00
--
INSERT INTO ly_sales_fact VALUES(1,1,1,10,3,15);
INSERT INTO ly_sales_fact VALUES(1,2,1,20,5,35);
INSERT INTO ly_sales_fact VALUES(2,2,1,40,15,135);
INSERT INTO ly_sales_fact VALUES(2,3,1,10,1,13);
--
SELECT * FROM ly_sales_summary_bytime;
DELETE   FROM ly_sales_fact WHERE product_key = 1;
SELECT * FROM ly_sales_summary_bytime;
UPDATE ly_sales_fact SET units_sold = units_sold * 2;
SELECT * FROM sales_summary_bytime;