Code:
CREATE TABLE values
(
sensor_id bigint NOT NULL,
"value" double precision,
value_text character varying(255),
value_time timestamp without time zone NOT NULL,
CONSTRAINT values_pkey PRIMARY KEY (sensor_id, value_time)
)
WITH (OIDS=FALSE);
CREATE TABLE values_avg
(
sensor_id bigint,
"day" date,
"hour" smallint,
value_avg double precision,
value_min double precision,
value_max double precision,
value_count bigint,
value_variance character varying,
value_stddev double precision,
"minute" smallint,
temp_avg double precision,
CONSTRAINT values_avg_sensor_id_key UNIQUE (sensor_id, day, hour, minute)
)
WITH (OIDS=FALSE);
CREATE TABLE sensors
(
id integer NOT NULL,
value_time timestamp without time zone,
value_cur double precision,
value_min double precision,
value_max double precision,
is_scalar smallint DEFAULT 0,
visible smallint DEFAULT 1,
value_before double precision,
value_beforetime timestamp without time zone,
CONSTRAINT sensors_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
und die Funktion: Code:
CREATE OR REPLACE FUNCTION insert_value(date2 timestamp without time zone, sensor2 integer,
value2 double precision)
RETURNS integer AS
$BODY$DECLARE
tmpmin double precision;
tmpmax double precision;
tmpval double precision;
tmpbefore double precision;
tmpdatebefore timestamp without time zone;
comma2 integer;
hour2 integer;
minute2 integer;
temp_avg2 double precision;
day2 date;
BEGIN
-- Hole alte Daten
SELECT value_min, value_max, value_cur, value_time INTO tmpmin, tmpmax, tmpbefore,
tmpdatebefore FROM sensors WHERE id=sensor2;
tmpval := value2;
-- Suche min, max
IF (tmpval<tmpmin) OR (tmpmin is NULL) THEN tmpmin := tmpval; END IF;
IF (tmpval>tmpmax) OR (tmpmax is NULL) THEN tmpmax := tmpval; END IF;
-- Wenn es der neuste ist Wert, dann Speicher ihn ab
IF ((tmpdatebefore is null OR tmpdatebefore<=date2)) THEN
UPDATE sensors SET value_time = date2, value_min = tmpmin, value_max = tmpmax, value_cur =
tmpval, value_before=tmpbefore, value_beforetime=tmpdatebefore WHERE id=sensor2;
END IF;
--Prüfe ob zu dieser Uhrzeit schon ein Wert viorhanden ist (aus Backups etc.)
SELECT value INTO temp_avg2 FROM values WHERE sensor_id=sensor2 AND value_time=date2;
IF FOUND THEN
UPDATE values SET value=tmpval WHERE sensor_id=sensor2 AND value_time=date2;
ELSE
INSERT INTO values (sensor_id, value, value_time) VALUES (sensor2, tmpval, date2);
END IF;
-- Bilde Mittelwerte für diese Minute
day2 := date2::date;
hour2 := date_part('hour',date2);
minute2 := date_part('minute',date2);
SELECT value_min, value_max, temp_avg INTO tmpmin, tmpmax, temp_avg2 FROM values_avg WHERE
day=day2 AND hour=hour2 AND minute=minute2 AND sensor_id=sensor2;
IF FOUND THEN
temp_avg2 := temp_avg2 + tmpval;
IF (tmpval<tmpmin) OR (tmpmin is NULL) THEN tmpmin := tmpval; END IF;
IF (tmpval>tmpmax) OR (tmpmax is NULL) THEN tmpmax := tmpval; END IF;
UPDATE values_avg SET temp_avg=temp_avg2, value_min = tmpmin, value_max = tmpmax,
value_count=value_count+1, value_avg = (temp_avg/value_count) WHERE sensor_id=sensor2 AND
day=day2 AND hour=hour2 AND minute=minute2;
ELSE
INSERT INTO values_avg (sensor_id, value_count, value_min, value_max, temp_avg, value_avg,
day, hour, minute) VALUES (sensor2, 1, tmpval, tmpval, tmpval, tmpval, day2, hour2, minute2);
END IF;
-- Bilde Mittelwerte für diese Stunde
minute2 := -1;
SELECT value_min, value_max, temp_avg INTO tmpmin, tmpmax, temp_avg2 FROM values_avg WHERE
day=day2 AND hour=hour2 AND minute=minute2 AND sensor_id=sensor2;
IF FOUND THEN
temp_avg2 := temp_avg2 + tmpval;
IF (tmpval<tmpmin) OR (tmpmin is NULL) THEN tmpmin := tmpval; END IF;
IF (tmpval>tmpmax) OR (tmpmax is NULL) THEN tmpmax := tmpval; END IF;
UPDATE values_avg SET temp_avg=temp_avg2, value_min = tmpmin, value_max = tmpmax,
value_count=value_count+1, value_avg = (temp_avg/value_count) WHERE sensor_id=sensor2 AND
day=day2 AND hour=hour2 AND minute=minute2;
ELSE
INSERT INTO values_avg (sensor_id, value_count, value_min, value_max, temp_avg, value_avg,
day, hour, minute) VALUES (sensor2, 1, tmpval, tmpval, tmpval, tmpval, day2, hour2, minute2);
END IF;
-- Bilde Mittelwerte für diesen Tag
hour2 := -1;
SELECT value_min, value_max, temp_avg INTO tmpmin, tmpmax, temp_avg2 FROM values_avg WHERE
day=day2 AND hour=hour2 AND minute=minute2 AND sensor_id=sensor2;
IF FOUND THEN
temp_avg2 := temp_avg2 + tmpval;
IF (tmpval<tmpmin) OR (tmpmin is NULL) THEN tmpmin := tmpval; END IF;
IF (tmpval>tmpmax) OR (tmpmax is NULL) THEN tmpmax := tmpval; END IF;
UPDATE values_avg SET temp_avg=temp_avg2, value_min = tmpmin, value_max = tmpmax,
value_count=value_count+1, value_avg = (temp_avg/value_count) WHERE sensor_id=sensor2 AND
day=day2 AND hour=hour2 AND minute=minute2;
ELSE
INSERT INTO values_avg (sensor_id, value_count, value_min, value_max, temp_avg, value_avg,
day, hour, minute) VALUES (sensor2, 1, tmpval, tmpval, tmpval, tmpval, day2, hour2, minute2);
END IF;
RETURN 0;
END; $BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Lesezeichen