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