-- createlang pgpgsql ebus

CREATE TABLE value_cache ( LIKE value );

DELETE FROM value_cache;

-- Create a function that always returns the first non-NULL item
CREATE OR REPLACE FUNCTION public.first_agg ( anyelement, anyelement ) RETURNS anyelement AS $$
       SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 END;
$$ LANGUAGE SQL STABLE;
 
-- And then wrap an aggreagate around it
CREATE AGGREGATE public.first (
       sfunc    = public.first_agg,
       basetype = anyelement,
       stype    = anyelement
);

DROP FUNCTION value_insert_trigger_func() CASCADE;

--CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$
--       BEGIN
--    DELETE FROM value_cache
--          WHERE date_trunc('hour', timestamp) = 
--                               (SELECT date_trunc('hour', max(timestamp)) FROM value)
--                AND sensor_id = (SELECT sensor_id from value where id = (select max(id) from value));
--
--    INSERT INTO value_cache
--               SELECT
--              COALESCE(0, (select max(id)+1 from value_cache)) as "id" --XXX use seq
--                 ,date_trunc('hour', timestamp) AS "timestamp"
--                 ,sensor_id AS "sensor_id"
--                 ,type AS "type"
--                 ,AVG(value_float) AS "value_float"
--                 ,AVG(value_int) AS "value_int"
--                 ,first(value_string) AS "value_string"
--                 FROM value
--             WHERE date_trunc('hour', timestamp) = (SELECT date_trunc('hour', max(timestamp)) FROM value)
--           AND sensor_id  = (SELECT sensor_id FROM value WHERE id = (SELECT MAX(id) FROM value))
--          GROUP BY type, sensor_id, date_trunc('hour', timestamp);               
--    RETURN NULL;
--        END;
--$$ LANGUAGE plpgsql;


CREATE FUNCTION value_insert_trigger_func() RETURNS trigger AS $$
       BEGIN
    NOTIFY evt_ebus_value_insert;
 
    RETURN NULL;
        END;
$$ LANGUAGE plpgsql;

-- DROP TRIGGER value_insert_trigger ON value;

CREATE TRIGGER value_insert_trigger
    AFTER INSERT ON value FOR EACH STATEMENT EXECUTE PROCEDURE value_insert_trigger_func();