PostgreSQL insert row with trigger function

357 Views Asked by At

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?

0

There are 0 best solutions below