63 lines
1.6 KiB
PL/PgSQL
63 lines
1.6 KiB
PL/PgSQL
/* Adding last values to the datapoint table since they are a regularly used value. */
|
|
ALTER TABLE public.datapoint ADD COLUMN last_value_id int4 NULL;
|
|
ALTER TABLE public.datapoint ADD COLUMN last_value_int int8 NULL;
|
|
ALTER TABLE public.datapoint ADD COLUMN last_value_string varchar NULL;
|
|
ALTER TABLE public.datapoint ADD COLUMN last_value_datetime timestamptz NULL;
|
|
|
|
|
|
|
|
/* Once-run query to update it to the latest, to avoid user having to wait for the next entry. */
|
|
UPDATE public.datapoint AS dp
|
|
SET
|
|
last_value_id = dpv.id,
|
|
last_value_int = dpv.value_int,
|
|
last_value_string = dpv.value_string,
|
|
last_value_datetime = dpv.value_datetime
|
|
FROM (
|
|
SELECT
|
|
dp.id AS datapoint_id,
|
|
dpv.id,
|
|
dpv.value_int,
|
|
dpv.value_string,
|
|
dpv.value_datetime
|
|
FROM public.datapoint dp
|
|
LEFT JOIN (
|
|
SELECT
|
|
*,
|
|
row_number() OVER (PARTITION BY "datapoint_id" ORDER BY ts DESC) AS rn
|
|
FROM datapoint_value
|
|
) dpv ON dpv.datapoint_id = dp.id AND rn = 1
|
|
) AS dpv
|
|
WHERE
|
|
dpv.datapoint_id = dp.id;
|
|
|
|
|
|
|
|
/* A trigger keeps the value current without bugs introduced in software missing the entry. */
|
|
CREATE OR REPLACE FUNCTION datapoint_entry()
|
|
RETURNS TRIGGER
|
|
LANGUAGE PLPGSQL
|
|
AS
|
|
$$
|
|
BEGIN
|
|
UPDATE public.datapoint
|
|
SET
|
|
nodata_is_problem = false,
|
|
last_value = NEW.ts,
|
|
last_value_id = NEW.id,
|
|
last_value_int = NEW.value_int,
|
|
last_value_string = NEW.value_string,
|
|
last_value_datetime = NEW.value_datetime
|
|
WHERE
|
|
id = NEW.datapoint_id;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE TRIGGER datapoint_entry
|
|
AFTER INSERT
|
|
ON public.datapoint_value
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE datapoint_entry();
|