postgresql 按小時分表（含觸發器）的實現方式

心机雅 發表於2020-06-13
SQL觸發器

本人後端開發，因為業務需求需要使用分表方式進行資料儲存。結合網上的資料最後使用的以下方式：

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;

我是按照小時對固定的一張表進行分表的，其實可以寫活就是加個變數，拼sql的時候拼接進去。這個就是寫個函式作為觸發器的回撥函式。就先判斷一下這個表有沒有，有就直接插，沒有就建表再插。

CREATE TRIGGER insert_tbl_partition_trigger
 BEFORE INSERT
 ON t_audit
 FOR EACH ROW
 EXECUTE PROCEDURE auto_insert_into_tbl_partition('time');

建一個觸發器，在父表的插入資料的時候執行前面的那個回撥函式。

ps：下面看下postgresql 表觸發器

1、先建一個函式，用來執行觸發器啟動後要執行的指令碼

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

2、建表的觸發器，

CREATE TRIGGER gk_site_day_insert BEFORE INSERT ON gk_site_day
  FOR EACH ROW EXECUTE PROCEDURE trigger_day_aqi();

總結

以上所述是小編給大家介紹的postgresql 按小時分表（含觸發器）的實現方式，希望對大家有所幫助。

相關文章