--天分区函数
CREATE OR REPLACE FUNCTION create_tab_part_day() RETURNS integer
LANGUAGE plpgsql AS
$$
DECLARE
--表名拼接串
dateStr varchar;
--起
startStr varchar;
--止
endStr varchar;
BEGIN
SELECT to_char(current_date+interval'1 day','YYYYMMDD') INTO dateStr;
SELECT to_char(current_date+interval'1 day', 'YYYY-MM-DD') INTO startStr;
SELECT to_char(current_date+interval'2 day', 'YYYY-MM-DD') INTO endStr;
EXECUTE
--根据需求在串内添加表空间 tablespace TBS_APTS_CM
format('CREATE TABLE BUSVIOLINFOGS_test_p%1$s PARTITION OF BZ_BUSVIOLINFOGS_test FOR VALUES FROM (%2$I) TO (%3$I) ;', dateStr,startStr,endStr);
RETURN 1;
END;
$$;
--删除插件
DROP EXTENSION pg_cron;
--生成插件
CREATE EXTENSION pg_cron;
--计划 每天创建第二天分区
SELECT cron.schedule('create-partitions-day1','18 * * * *', $$SELECT create_tab_part_day();$$);
SELECT cron.schedule('create-partitions-day2','50 * * * *', $$SELECT create_tab_part_day();$$);
--计划 每月创建第二月分区
SELECT cron.schedule('create-partitions-month','0 1 1 * *', $$SELECT create_tab_part_month();$$);
--计划 每年创建第二年分区
SELECT cron.schedule('create-partitions-year','0 1 1 1 *', $$SELECT create_tab_part_year();$$);
--查看定时计划
select jobid, command from cron.job;
select * from cron.job;
--查看cron配置
select name,setting from pg_settings where name like '%cron%';
SELECT * from pg_settings
--删除定时计划
select cron.unschedule('create-partitions-day1' );
select cron.unschedule('create-partitions-day2' );
--表名拼接串 日 月 年
SELECT to_char(current_date+interval'1 day','YYYYMMDD') INTO dateStr;
SELECT to_char(date_trunc('month',current_date)+interval'1 month','YYYYMM') INTO dateStr;
SELECT to_char(date_trunc('year',current_date)+interval'1 year','YYYY') INTO dateStr;
--按天分区起止时间串
to_char(current_date+interval'1 day', 'YYYY-MM-DD') to_char(current_date+interval'2 day', 'YYYY-MM-DD')
--按月分区起止时间串
to_char(date_trunc('month',current_date)+interval'1 month', 'YYYY-MM-DD') to_char(date_trunc('month',current_date)+interval'2 month', 'YYYY-MM-DD')
--按年分区起止时间串
to_char(date_trunc('year',current_date)+interval'1 year', 'YYYY-MM-DD') to_char(date_trunc('year',current_date)+interval'2 year', 'YYYY-MM-DD')
cron时间表达式以及pg_cron插件安装方法请自行搜索