forum.lissyara.su

Код: Выделить всё

CREATE TABLE actions (
    username character varying(64) DEFAULT ''::character varying NOT NULL,
    uid bigint DEFAULT 0::bigint NOT NULL,
    in_bytes bigint DEFAULT 0::bigint NOT NULL,
    out_bytes bigint DEFAULT 0::bigint NOT NULL
);

CREATE TABLE graph_data (
    graph_id bigserial NOT NULL,
    id_user bigint DEFAULT 0::bigint NOT NULL,
    new_in_bytes bigint DEFAULT 0::bigint NOT NULL,
    new_out_bytes bigint DEFAULT 0::bigint NOT NULL
);

Код: Выделить всё

CREATE OR REPLACE FUNCTION OnActionsUpdate() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.in_bytes!=OLD.in_bytes or NEW.out_bytes!=OLD.out_bytes THEN
        INSERT INTO graph_data(id_user,new_in_bytes,new_out_bytes) VALUES(NEW.uid, NEW.in_bytes-OLD.in_bytes, NEW.out_bytes-OLD.out_bytes);
    END IF;
    RETURN NULL;
END;$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_OnActionsUpdate AFTER UPDATE ON actions FOR EACH ROW EXECUTE PROCEDURE OnActionsUpdate();

Код: Выделить всё

CREATE RULE "_RETURN" AS
    ON UPDATE TO actions DO INSTEAD 
    INSERT INTO graph_data (id_user, new_in_bytes, new_out_bytes) VALUES(OLD.uid, NEW.in_bytes-OLD.in_bytes, NEW.out_bytes-OLD.out_bytes);