自动创建分区表及触发器

为什么80%的码农都做不了架构师？>>>   

分区表自动生成脚本

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;

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

转载于:https://my.oschina.net/sucre/blog/742856