CREATE TABLE detail
(
   date_det date NOT NULL,
   src_ip inet NOT NULL,
   dst_ip inet NOT NULL,
   bts int4 DEFAULT  0 ,
   src_int int2,
   dst_int int2
) WITH OIDS;

CREATE TABLE traffic
(
   date date NOT NULL,
   src inet NOT NULL,
   dst inet NOT NULL,
   inc int4,
   out int4
) WITH OIDS;

CREATE OR REPLACE FUNCTION detail_insert() RETURNS TRIGGER AS $$
DECLARE
        s_ip inet;
        d_ip inet;
        in_b int;
        ou_b int;
BEGIN

-- Switch ip
IF (NEW.src_ip <<= '172.16.0.0/16' OR NEW.src_ip <<= '62.231.26.0/24') THEN
        s_ip = NEW.src_ip;
        d_ip = NEW.dst_ip;
        in_b =  0 ;
        ou_b = NEW.bts;
ELSE
        s_ip = NEW.dst_ip;
        d_ip = NEW.src_ip;
        in_b = NEW.bts;
        ou_b =  0 ;
END IF;

IF (NEW.date_det IN (SELECT date FROM traffic) AND s_ip IN (SELECT src FROM
traffic WHERE date=NEW.date_det)
AND d_ip IN (SELECT dst FROM traffic WHERE date=NEW.date_det AND src=s_ip))
THEN
        UPDATE traffic SET inc = traffic.inc + in_b, out = traffic.out + ou_b
                WHERE traffic.date = new.date_det AND traffic.src = s_ip AND traffic.dst = d_ip;
ELSE
        INSERT INTO traffic VALUES(NEW.date_det, s_ip, d_ip, in_b, out_b);
END IF;

RETURN NULL;

END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER on_insert_detail
AFTER INSERT ON detail
        FOR EACH ROW EXECUTE PROCEDURE detail_insert();