postgresql 自定义函数_sql

概述CREATE OR REPLACE FUNCTION "public"."pm25_aqi"("pm25_nd" numeric) RETURNS "pg_catalog"."int4" AS $BODY$DECLARE RET numeric; IAQI_1 numeric; IAQI_2 numeric; IAQI_3 numeric;

CREATE OR REPLACE FUNCTION "public"."pm25_aqi"("pm25_nd" numeric)  RETURNS "pg_catalog"."int4" AS $BODY$DECLARE    RET numeric;    IAQI_1 numeric;    IAQI_2 numeric;    IAQI_3 numeric;    IAQI_4 numeric;    IAQI_5 numeric;    IAQI_6 numeric;    IAQI_7 numeric;    IAQI_8 numeric;    BP_1 numeric;    BP_2 numeric;    BP_3 numeric;    BP_4 numeric;    BP_5 numeric;    BP_6 numeric;    BP_7 numeric;    BP_8 numeric;BEGIN    IAQI_1=0;    IAQI_2=50;    IAQI_3=100;    IAQI_4=150;    IAQI_5=200;    IAQI_6=300;    IAQI_7=400;    IAQI_8=500;    BP_1=0;    BP_2=35;    BP_3=75;    BP_4=115;    BP_5=150;    BP_6=250;    BP_7=350;    BP_8=500;          IF   PM25_ND<=BP_2       THEN RET = ((IAQI_2-IAQI_1)/(BP_2-BP_1))*(PM25_ND-BP_1)+IAQI_1;        ELSIF PM25_ND>BP_2 AND PM25_ND<=BP_3      THEN  RET = ((IAQI_3-IAQI_2)/(BP_3-BP_2))*(PM25_ND-BP_2)+IAQI_2;        ELSIF PM25_ND>BP_3 AND PM25_ND<=BP_4       THEN RET = ((IAQI_4-IAQI_3)/(BP_4-BP_3))*(PM25_ND-BP_3)+IAQI_3;        ELSIF PM25_ND>BP_4 AND PM25_ND<=BP_5       THEN RET = ((IAQI_5-IAQI_4)/(BP_5-BP_4))*(PM25_ND-BP_4)+IAQI_4;        ELSIF PM25_ND>BP_5 AND PM25_ND<=BP_6      THEN  RET = ((IAQI_6-IAQI_5)/(BP_6-BP_5))*(PM25_ND-BP_5)+IAQI_5;        ELSIF PM25_ND>BP_6 AND PM25_ND<=BP_7      THEN  RET = ((IAQI_7-IAQI_6)/(BP_7-BP_6))*(PM25_ND-BP_6)+IAQI_6;        ELSIF PM25_ND>BP_7        THEN RET = ((IAQI_8-IAQI_7)/(BP_8-BP_7))*(PM25_ND-BP_7)+IAQI_7;    END IF;        IF RET>500 THEN RET=500;  END IF;    return  ceiling(RET);                                     END;$BODY$  LANGUAGE plpgsql VolATILE  COST 100