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