[大数据]数据库删除一年前的数据-函数、函数触发器

第一步：创建一张表

CREATE TABLE "public"."t_ticket_create_log" (
  "id" numeric(20) NOT NULL,
  "domain_path" varchar(200) COLLATE "pg_catalog"."default",
  "label" varchar(200) COLLATE "pg_catalog"."default",
  "task_category" varchar(8) COLLATE "pg_catalog"."default",
  "device_ids" varchar(200) COLLATE "pg_catalog"."default",
  "task_id" numeric(20),
  "task_commit_time" timestamp(6),
  "result" numeric(2),
  "reason" varchar(2048) COLLATE "pg_catalog"."default",
  "create_time" timestamp(6),
  "modify_time" timestamp(6),
  CONSTRAINT "t_ticket_create_log_pkey" PRIMARY KEY ("id")
);

第二步：编写函数-删除一年前的数据

CREATE OR REPLACE FUNCTION auto_del_t_ticket_create_log()
RETURNS TRIGGER AS $res$
BEGIN
   delete from t_ticket_create_log where task_commit_time <= (select now()-interval '1 year' a);
	 return NULL;
END;
$res$ LANGUAGE plpgsql;

第三步：创建触发器-触发删除一年前的数据&函数

CREATE TRIGGER auto_del_ticket_create_log AFTER INSERT ON t_ticket_create_log
FOR EACH ROW EXECUTE PROCEDURE auto_del_t_ticket_create_log();