smon/sql/00026.sql

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