自动创建分区表及触发器

前端之家收集整理的这篇文章主要介绍了自动创建分区表及触发器前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

分区表自动生成脚本

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;

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

猜你在找的Postgre SQL相关文章