分区表自动生成脚本
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年的分区,每个分区表是以月为后缀的,-72...72这个是时间范围可以自行修改。 再来看看自动创建触发器的脚本
CREATE FUNCTION "public"."create_table_insert_trigger"(IN tablename varchar) RETURNS "text"
AS $BODY$
declare
date_suffix varchar(6);
sqlCon varchar;
p_table varchar;
tmp integer :=0;
sqlTrigger varchar;
begin
sqlCon = 'CREATE OR REPLACE FUNCTION '||tablename||'_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
';
sqlTrigger = 'CREATE TRIGGER trg_insert_'||tablename||' BEFORE INSERT ON "public"."'||tablename||'" FOR EACH ROW EXECUTE PROCEDURE "'||tablename||'_insert_trigger"();
COMMENT ON TRIGGER "trg_insert_'||tablename||'" ON "public"."'||tablename||'" IS NULL;';
for i in -72..72 loop
select to_char(now() + interval '1 month' * i,'yyyyMM') into date_suffix;
p_table='p_'||tablename||'_'||date_suffix;
IF tmp=0 THEN
sqlCon = sqlCon || 'IF ( NEW.finance_month = '||date_suffix||') THEN
INSERT INTO '||p_table||' VALUES (NEW.*);
';
tmp =1;
ELSE
sqlCon = sqlCon || 'ELSIF ( NEW.finance_month = '||date_suffix||') THEN
INSERT INTO '||p_table||' VALUES (NEW.*);
';
END if;
end loop;
sqlCon = sqlCon || 'END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;';
EXECUTE sqlCon;
EXECUTE sqlTrigger;
RETURN sqlCon;
end;
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;
这个脚本会将前面通过分区脚本创建的分区表中加上相应的触发器,好了,脚本很好理解就不多说了,有问题请留言。