分区表自动生成脚本

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;

这个脚本会将前面通过分区脚本创建的分区表中加上相应的触发器，好了，脚本很好理解就不多说了，有问题请留言。