postgresql 按小时分表（含触发器）的实现方式

CREATE OR REPLACE FUNCTION auto_insert_into_tbl_partition()

  RETURNS trigger AS

$BODY$

DECLARE

   time_column_name   text ;      -- 父表中用于分区的时间字段的名称[必须首先初始化!!]

   curMM     varchar(16);    -- 'YYYYMM'字串,用做分区子表的后缀

   isExist     boolean;    -- 分区子表,是否已存在

   strSQL     text;

BEGIN

   -- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]

   time_column_name := TG_ARGV[0];

   -- 判断对应分区表 是否已经存在?

   EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;

   curMM := to_char( strSQL::timestamp , 'YYYYMMDDHH' );

   select count(1) INTO isExist from pg_class where relname = ('t_audit_'||curMM);

   -- 若不存在, 则插入前需 先创建子分区

   IF ( isExist = false ) THEN

     -- 创建子分区表

     strSQL := 'CREATE TABLE IF NOT EXISTS t_audit_'||curMM||'() INHERITS (t_audit);' ;

     EXECUTE strSQL;

     -- 创建索引

     strSQL := 'CREATE INDEX t_audit_'||curMM||'_INDEX_'||time_column_name||' ON t_audit_'||curMM||' ('||time_column_name||');' ;

     EXECUTE strSQL;

   END IF;

   -- 插入数据到子分区!

   strSQL := 'INSERT INTO t_audit_'||curMM||' SELECT $1.*' ;

   EXECUTE strSQL USING NEW;

   RETURN NULL;

END

$BODY$

  LANGUAGE plpgsql;

CREATE TRIGGER insert_tbl_partition_trigger

  BEFORE INSERT

  ON t_audit

  FOR EACH ROW

  EXECUTE PROCEDURE auto_insert_into_tbl_partition('time');

CREATE OR REPLACE FUNCTION "public"."trigger_day_aqi"()

  RETURNS "pg_catalog"."trigger" AS $BODY$

BEGIN

   --日均值表，没有o3,小时值表，没有o3_8h

    NEW.so2iaqi=DAY_SO2_AQI(NEW.so2);

      NEW.no2iaqi=DAY_NO2_AQI(NEW.no2);

      NEW.coiaqi=DAY_CO_AQI(NEW.co);

      NEW.o3_8hiaqi=O3_8_AQI(NEW.o3_8h);

      NEW.pm10iaqi=PM10_AQI(NEW.pm10);

      NEW.pm25iaqi=PM25_AQI(NEW.pm25);

      NEW.aqi=NEW.coiaqi;

      NEW.primarypol='CO';

     IF NEW.aqi<NEW.no2iaqi  then  NEW.aqi=NEW.no2iaqi;  NEW.primarypol='NO2';  end if;

     IF NEW.aqi<NEW.so2iaqi  then  NEW.aqi=NEW.so2iaqi;  NEW.primarypol='SO2';  end if;

     IF NEW.aqi<NEW.o3_8hiaqi then  NEW.aqi=NEW.o3_8hiaqi;  NEW.primarypol='O3_8H'; end if;

     IF NEW.aqi<NEW.pm10iaqi then  NEW.aqi=NEW.pm10iaqi;  NEW.primarypol='PM10';  end if;

     IF NEW.aqi<NEW.pm25iaqi then  NEW.aqi=NEW.pm25iaqi;  NEW.primarypol='PM2.5'; end if;

     IF NEW.aqi<=50      then  NEW.primarypol='-';   end if;

      NEW.aqilevel=getRank(NEW.aqi);

   RETURN NEW;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100

CREATE TRIGGER gk_site_day_insert BEFORE INSERT ON gk_site_day

   FOR EACH ROW EXECUTE PROCEDURE trigger_day_aqi();