I have a table (machines_data_paired) like this one:
timestamp | id | data |
---|---|---|
2022-07-01 17:23:13.437 | 1 | {'foo': 'bar'} |
2022-07-01 17:23:14.937 | 2 | {'foo': 'bar'} |
2022-07-01 17:23:15.437 | 1 | {'foo': 'bar'} |
When a new data element is inserted in this table, I want to put the element in an other table (machines_data_paired_10_min), but only if the delta timestamp between the NEW element and the latest element stored (with the same id) is greater than 600 seconds.
This is the trigger create:
DROP TRIGGER IF EXISTS trig_machines_data_paired_10_min ON public.machines_data_paired;
CREATE TRIGGER trig_machines_data_paired_10_min
BEFORE INSERT
ON public.machines_data_paired
FOR EACH ROW
EXECUTE FUNCTION public.insert_10_min();
And this the trigger function create:
CREATE OR REPLACE FUNCTION public.insert_10_min()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE t1 timestamp;
DECLARE delta real;
BEGIN
SELECT INTO t1 (SELECT timestamp from machines_data_paired_10_min WHERE machine_id = NEW.machine_id order by timestamp desc limit 1);
SELECT INTO delta (EXTRACT(EPOCH FROM(NEW.timestamp - t1)));
IF (delta > 600 OR delta = null) as res THEN
INSERT INTO machines_data_paired_10_min(
"timestamp", data, machine_id)
VALUES (NEW.timestamp, NEW.data, NEW.machine_id);
END IF;
RETURN NEW;
END
$BODY$;
ALTER FUNCTION public.insert_10_min()
OWNER TO postgres;
I can't make it work. What am I missing?