postgresql表数据监控设计

1.创建logging模式,并且创建一个logging模式下的记录表:

CREATE SCHEMA logging;

CREATE TABLE logging.user_history (
    id serial,
    tstamp timestamp DEFAULT now(),
    schemaname text,
    tabname text,
    operation text,
    who text DEFAULT current_user,
    new_val json,
    old_val json
);

2.创建触发器函数:

CREATE FUNCTION change_user_trigger()
    RETURNS trigger AS $$
    BEGIN
        IF TG_OP = ‘INSERT‘ THEN
            INSERT  INTO logging.user_history(tabname,schemaname,operation,new_val)  values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP,row_to_json(NEW));
        RETURN NEW;
        ELSIF TG_OP = ‘UPDATE‘  THEN
                INSERT INTO logging.user_history(tabname,schemaname,operation,new_val,old_val) values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP,row_to_json(NEW),row_to_json(OLD));
        RETURN NEW;
        ELSIF TG_OP = ‘DELETE‘  THEN
              INSERT INTO logging.user_history(tabname,schemaname,operation, old_val) values(TG_RELNAME,TG_TABLE_SCHEMA,TG_OP,row_to_json(OLD));
	RETURN OLD; --返回值要与ELSIF平齐,因为先插入后最好才执行返回
	END IF;
END;
$$ LANGUAGE ‘plpgsql‘ SECURITY DEFINER;--security definer是指定创建该函数用户的权限执行,security invoker是指以调用该函数用户发权限执行

3.创建测试表users:

CREATE TABLE users
(
  id serial NOT NULL,
  username character varying(40),
  email character varying(100)
)

4.创建触发器:

CREATE TRIGGER "logging_user_change"
  BEFORE INSERT OR UPDATE OR DELETE
  ON users
  FOR EACH ROW
  EXECUTE PROCEDURE change_user_trigger();

5.测试：

INSERT INTO users VALUES ( ‘hans‘,‘[email protected]‘);
UPDATE users SET id = 1, username =‘paul‘;