postgrep 更新插入插入 自动分区

1.建表：

CREATE TABLE qpf (
 lon double precision,-- lon 值
 lat double precision,-- lat 值
 rain double precision,-- lat 值
 time timestamp,  -- timestamp
 publictime  varchar -- 发布时间
 )
;

2.函数

CREATE OR REPLACE FUNCTION qpf_partition_time_function()  
  RETURNS trigger AS  
$BODY$  
DECLARE tbl_name CHARACTER VARYING;  
    tbl_name_main CHARACTER VARYING='qpf';  
    tbl_name_salve CHARACTER VARYING;  
    tbl_year INTEGER;  
    tbl_month INTEGER; 
tbl_day INTEGER;
time_next_day TIMESTAMP;
    create_tbl_sql CHARACTER VARYING;  
    insert_tbl_sql CHARACTER VARYING;  
    index_sql CHARACTER VARYING;  
_has int ; 
BEGIN  


select count(1) from qpf where lat = NEW.lat and lon=NEW.lon and time=NEW.time into _has; 
raise notice 'ddd:%' , _has; 
if _has > 0 then 
update qpf set rain = NEW.rain ,publictime=NEW.publictime where lat = NEW.lat and lon=NEW.lon and time=NEW.time; 
ElSE
SELECT date_part('year',NEW.time::date)  INTO tbl_year;  
SELECT date_part('month',NEW.time::date) INTO tbl_month; 
SELECT date_part('day',NEW.time::date) INTO tbl_day;
IF(tbl_month<10)THEN
IF(tbl_day<10) THEN
tbl_name_salve=tbl_year||'0'||tbl_month||'0'||tbl_day; 
ELSE
tbl_name_salve=tbl_year||'0'||tbl_month||tbl_day; 
END IF;
ELSE   
  IF(tbl_day<10) THEN
tbl_name_salve=tbl_year||tbl_month||'0'||tbl_day; 
 ELSE
tbl_name_salve=tbl_year||tbl_month||tbl_day; 
END IF;
END IF;  
tbl_name=tbl_name_main||'_'||tbl_name_salve;  
 
--判断表 时间分段表是否存在  
IF ( (SELECT count(1) FROM pg_class WHERE relname=tbl_name)>0) THEN  
insert_tbl_sql='INSERT INTO '||tbl_name ||' VALUES('||NEW.lon||','||NEW.lat||','||NEW.rain||', timestamp'''||NEW.time||''','''||NEW.publictime||''')';  
EXECUTE insert_tbl_sql;  
ELSE   
   time_next_day = NEW.time + '1 days';
create_tbl_sql='CREATE TABLE '||tbl_name  
||'('  
||  'CONSTRAINT '||tbl_name||'_time_check'||' CHECK (time >= timestamp '''||NEW.time||''' AND time <timestamp '''||time_next_day||'s'')'  
||')INHERITS (qpf)';  
EXECUTE create_tbl_sql;  
--索引  
index_sql='CREATE INDEX '||tbl_name||'_time_index ON '||tbl_name||'(time,lon,lat)';  
EXECUTE index_sql;  
 
insert_tbl_sql='INSERT INTO '||tbl_name ||' VALUES('||NEW.lon||','||NEW.lat||','||NEW.rain||', timestamp'''||NEW.time||''','''||NEW.publictime||''')';  
EXECUTE insert_tbl_sql;  
END IF;  
END IF;
    RETURN NULL;  
END; 
$BODY$ 
LANGUAGE PLPGSQL; 

3.关联

CREATE TRIGGER qpf_before BEFORE INSERT ON qpf FOR EACH ROW EXECUTE PROCEDURE qpf_partition_time_function();