Postgres14自动按照时间分表

1.创建一个数据库表

CREATE TABLE public.tabpartition
(
dataid integer,
dataname character varying(20),
datatime timestamp without time zone,
PRIMARY KEY (dataid)
);

ALTER TABLE IF EXISTS public.tabpartition
OWNER to postgres;

COMMENT ON TABLE public.tabpartition
IS '分区的母表';
2.在数据库表的上一级的下面找到触发器，新建一个触发器

CREATE OR REPLACE FUNCTION public.auto_insert_partition()
RETURNS trigger
LANGUAGE 'plpgsql'
VOLATILE
COST 100
AS $BODY$
DECLARE
time_column_name text ; -- 父表中用于分区的时间字段的名称[必须首先初始化!!]
curMM varchar(6); -- 'YYYYMM'字串,用做分区子表的后缀
isExist boolean; -- 分区子表,是否已存在
startTime text;
endTime text;
strSQL text;

BEGIN
-- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]
time_column_name := TG_ARGV[0];
-- 判断对应分区表 是否已经存在?
EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;
curMM := to_char( strSQL::timestamp , 'YYYYMM' );
select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||curMM);

-- 若不存在, 则插入前需 先创建子分区
IF ( isExist = false ) THEN
-- 创建子分区表
startTime := curMM||'01 00:00:00.000';
endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');
strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||curMM||
' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '
||time_column_name||'< '''|| endTime ||''' )
) INHERITS ('||TG_RELNAME||') ;' ;
EXECUTE strSQL;

-- 创建索引
strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||curMM||'_INDEX_'||time_column_name||' ON '
||TG_RELNAME||'_'||curMM||' ('||time_column_name||');' ;
EXECUTE strSQL;

END IF;

-- 插入数据到子分区!
strSQL := 'INSERT INTO '||TG_RELNAME||'_'||curMM||' SELECT $1.*' ;
EXECUTE strSQL USING NEW;
RETURN NULL;
-- INSERT INTO TG_RELNAME||'_'||curMM VALUES (NEW.*)
-- RETURN NULL;
END
$BODY$;
3.找到表tabpartition，展开他，找到触发器，新建触发器

CREATE TRIGGER autoinserttabpartition
BEFORE INSERT
ON public.tabpartition
FOR EACH ROW
EXECUTE FUNCTION public.auto_insert_partition(datatime);

COMMENT ON TRIGGER autoinserttabpartition ON public.tabpartition
IS '插入触发器';
4.插入一条数据测试下会不会自动分区

INSERT INTO public.tabpartition (dataid, dataname, datatime) VALUES (5, '123', '2022-02-01 11:11:11');
5.按照规则，可以插入其他月份的，执行后刷新下表，就看到不一样的表名了，比如我的

tabpartition_202202

6.测试好了后，多了很多分表，如何批量删除

CREATE FUNCTION del_ora_table() RETURNS void AS $$
DECLARE
tmp VARCHAR(512);
DECLARE names CURSOR FOR
select tablename from pg_tables where schemaname='public' and tablename like 'tabpartition_%';
BEGIN
FOR stmt IN names LOOP
tmp := 'DROP TABLE '|| quote_ident(stmt.tablename) || ' CASCADE;';
RAISE NOTICE 'notice: %', tmp;
EXECUTE 'DROP TABLE '|| quote_ident(stmt.tablename) || ' CASCADE;';
END LOOP;
RAISE NOTICE 'finished .....';
END;

$$ LANGUAGE plpgsql;
7.然后执行 select del_ora_table();
————————————————
版权声明：本文为CSDN博主「登录获取更多信息」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/Apeart/article/details/123068200