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();
