分区表自动生成脚本
CREATE FUNCTION "public"."create_table_partition"(IN tablename varchar) RETURNS "text"
AS $BODY$
declare
date_suffix varchar(6);
sqlCon varchar;
p_table varchar;
begin
for i in -72..72 loop
select to_char(now() + interval '1 month' * i,'yyyyMM') into date_suffix;
p_table='p_'||tablename||'_'||date_suffix;
raise notice '%',p_table;
sqlCon = 'create table '||p_table||' (
check ( finance_month = '||date_suffix||' )
) INHERITS ('||tablename||')';
EXECUTE sqlCon;
end loop;
RETURN date_suffix;
end;
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;
这个主要是创建前后6年的分区,每个分区表是以月为后缀的ÿ