-- Function: wsn_dynamic_amount_power(character varying,character varying,integer,numeric) -- DROP FUNCTION wsn_dynamic_amount_power(character varying,numeric); CREATE OR REPLACE FUNCTION wsn_dynamic_amount_power(app_id character varying,area_id character varying,node_id integer,data numeric) RETURNS boolean AS $BODY$ DECLARE node integer; sensor_data numeric(8,0); use_data numeric(8,0); work_time numeric(8,0); table_name1 varchar; table_name2 varchar; max_time time; min_time time; array_data varchar; id integer; BEGIN table_name1 = 'app_' || app_id || '_area_' || area_id || '_generation_power'; table_name2 = 'app_' || app_id || '_area_' || area_id || '_use_power'; --RAISE NOTICE '%',curr_date; IF node_id='30' OR node_id='31' OR node_id='32' THEN /* RAISE NOTICE '%',table_name1; */ EXECUTE 'SELECT generation_power FROM '|| table_name1 ||' WHERE node_id= ' || node_id ||' AND receive_date='''|| current_date ||'''' INTO sensor_data; /* RAISE NOTICE '%',sensor_data; RAISE NOTICE '%',data; RAISE NOTICE '%',current_date; */ IF sensor_data IS NULL THEN /* RAISE NOTICE '%','11OK'; */ EXECUTE 'INSERT INTO '|| table_name1 || ' (node_id,receive_date,generation_power) VALUES' || '(' || node_id || ',''' || current_date || ''',''' || data || ''')'; ELSE sensor_data := sensor_data + data; EXECUTE 'UPDATE ' || table_name1 || ' SET generation_power=''' || sensor_data || ''' WHERE receive_date=''' || current_date || ''' AND node_id=' || node_id; END IF; END IF; IF node_id='37' THEN EXECUTE 'SELECT id,use_power FROM '|| table_name2 || ' WHERE node_id= ' || node_id || ' AND receive_date='''|| current_date ||'''' INTO id,use_data; IF id IS NULL THEN /* RAISE NOTICE '%','11OK'; */ EXECUTE 'INSERT INTO '|| table_name2 || ' (node_id,use_power) VALUES' || '(' || node_id || ',' || data || ')'; ELSE use_data := use_data + data; EXECUTE 'UPDATE ' || table_name2 || ' SET use_power=''' || use_data || ''' WHERE receive_date=''' || current_date || ''' AND node_id=''' || node_id ||''' AND id=''' || id || ''''; END IF; END IF; RETURN true; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION wsn_dynamic_amount_power(character varying,numeric) OWNER TO wsn; 原文链接：https://www.f2er.com/postgresql/196286.html