建立触发器 trigger
CREATE TRIGGER datenew
AFTER INSERT OR UPDATE
ON data
FOR EACH ROW
EXECUTE PROCEDURE datatriger();
触发器函数
CREATE OR REPLACE FUNCTION datatriger()
RETURNS trigger AS
$BODY$
DECLARE
gatewayID INTEGER; --数据所属网关ID
tableExist INTEGER; --表是否存在
ll INTEGER; --记录条数
tableName TEXT; --表名
pkName TEXT; --主键名称
query TEXT;
BEGIN
SELECT gateway_id INTO gatewayID FROM gateway_conf WHERE gateway_logo=NEW.gateway_logo;
IF FOUND THEN
tableName := 'iot_history_'||gatewayID;
SELECT count(*) INTO tableExist FROM pg_statio_user_tables WHERE relname=tableName;
-- 如果表不存在则建表
IF tableExist=0 THEN
pkName := 'iot_history_pkey_'||gatewayID;
query := 'CREATE TABLE '||tableName||'
(
id serial NOT NULL,
data_time timestamp without time zone,
gateway_logo character(50),
sensor_name integer,
channel_name integer,
value numeric(6,2),
CONSTRAINT '||pkName||' PRIMARY KEY (id )
)';
EXECUTE query;
query := 'CREATE INDEX idx_his_'||gatewayID||' ON '||tableName||' (id ASC NULLS LAST)';
EXECUTE query;
END IF;
-- 转存数据
query := 'INSERT INTO '||tableName||' (data_time, gateway_logo, sensor_name, channel_name, value) VALUES ('''||NEW.data_time||''', '''||NEW.gateway_logo||''', '||NEW.sensor_name||', '||NEW.channel_name||', '||NEW.value||')';
EXECUTE query;
-- 删除历史数据
SELECT limits INTO ll FROM gateway_conf WHERE gateway_logo=NEW.gateway_logo;
IF ll<1000 THEN
ll := 10000;
END IF;
query := 'DELETE FROM '||tableName||' WHERE id < (SELECT MAX(id)-'||ll||' FROM '||tableName||')';
EXECUTE query;
END IF;
return NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION datatriger()
OWNER TO postgres;
