PostgreSQL Traffic Trigger

pgtraffictrigger.plsql

CREATE FUNCTION update_traffic_per_ip()
RETURNS trigger AS $traffic_update$
DECLARE
    ip INET;
    input DOUBLE PRECISION;
    output DOUBLE PRECISION;
    total DOUBLE PRECISION;
    month VARCHAR(2);
    year SMALLINT;
BEGIN
    ip := NEW.ip;
    year := EXTRACT("YEAR" FROM NEW.time - Interval '15 minutes');
    month := to_char(EXTRACT("MONTH" FROM NEW.time - Interval '15 minutes'), 'FM00');
    EXECUTE 'SELECT input, output, total
        FROM traffic_' || year || '_' || month || '_per_ip
        WHERE ip = $1'
        INTO input, output, total
        USING ip;
    IF input > 0 OR output > 0 THEN
        input := input + NEW.input;
        output := output + NEW.output;
        total := input + output;
        EXECUTE 'UPDATE traffic_' || year || '_' || month || '_per_ip SET input = $1, output = $2, total = $3, time = $4 WHERE ip = $5'
        USING input, output, total, NEW.time, ip;
    ELSE
        EXECUTE 'INSERT INTO traffic_' || year || '_' || month || '_per_ip VALUES($1, $2, $3, $4, $5)'
        USING ip, NEW.input, NEW.output, NEW.input + NEW.output, NEW.time;
    END IF;
    RETURN NEW; END;
$traffic_update$ LANGUAGE plpgsql;