【PostgreSQL】PostgreSQL监控表数据达到一定数量自动删除历史数据-触发器实现

说明

当employees数量大于1w的时候，删除1000之后的数据，这个要怎么搞啊

1、当表的记录大于1w，只保留最新1000条数据，已create_date字段来排序保留最新的1000

创建测试数据

--创建测试表
create table employees
    ( employee_id    int8    primary key
    , first_name     varchar(20)
    , last_name      varchar(25) constraint     emp_last_name_nn  not null
    , email          varchar(25) constraint     emp_email_nn  not null
    , phone_number   varchar(20)
    , hire_date      date         constraint     emp_hire_date_nn  not null
    , job_id         int8         constraint     emp_job_nn  not null
    , salary         numeric(8,2)
    , commission_pct numeric(4,2)
    , manager_id     int8
    , department_id  int8
    , constraint     emp_salary_min check (salary > 0) 
    ,create_date  timestamp
    ) ;
--生成测试数据
insert into employees
select generate_series(1,10010) as key,
       substr(md5(random()::text),2,5),
       substr(md5(random()::text),2,8),
       substr(md5(random()::text),2,5)||'@163.com',
       concat('1',ceiling(random()*9000000000+1000000000)),
       date((random()*(2022-1990)+1990)::int||'-'||(random()*(12-1)+1)::int||'-'||(random()*(28-1)+1)::int),
       (random()*(50-10)+10)::int,
       (random()*(10000-3000)+3000)::numeric(8,2),
       (random()*(1-0)+0)::numeric(4,2),
       (random()*(100-1)+1)::int,
       (random()*(10-1)+1)::int,
       concat(current_date - floor((random() * 25))::int,' ',make_time(floor((random() * 12))::int, floor((random() * 60))::int, floor((random() * 60))::int))::timestamp;

创建函数及触发器

drop FUNCTION trigger_employees_delete_rows_function cascade;
CREATE OR REPLACE FUNCTION trigger_employees_delete_rows_function() 
  RETURNS trigger 
  LANGUAGE plpgsql AS $$ 
declare 
  cnt int8 ;
  row_max int8 :=10000;
  rows_keep int8 :=1000;
begin
  select count(*) into cnt from employees;
  if cnt > row_max then
    delete from employees t1 
     using (select employee_id,create_date,row_number() over(order by create_date desc) as rn from employees ) as t2
     where t2.rn>rows_keep
       and t1.employee_id=t2.employee_id;
  end if;
  RETURN OLD;
END;
$$;

drop trigger   employees_delete_rows_trigger ON employees ;
create trigger employees_delete_rows_trigger 
BEFORE insert 
    on employees
execute procedure trigger_employees_delete_rows_function();

本质就是1条删除命令

delete from employees t1 using (select employee_id,create_date,row_number() over(order by create_date desc) as rn from employees ) as t2
 where t2.rn>1000
	  and t1.employee_id=t2.employee_id;

随便insert一条记录，表中数据量就会变成1000条了