0

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?

gpare
  • 41
  • 1
  • 7
  • 2
    `delta = null` should be `delta IS NULL` – Bergi Jul 01 '22 at 15:54
  • 1
    Define not working? – Adrian Klaver Jul 01 '22 at 15:55
  • @Bergi Thank you for the suggestion, I changed the function code – gpare Jul 05 '22 at 12:30
  • @AdrianKlaver I'm able to create the trigger and the function, but while all data are recorded in the machines_data_paired table, the machines_data_paired_10_min table remains empty. – gpare Jul 05 '22 at 12:33
  • 1
    1) You have not changed `delta = null` to `delta IS NULL`. 2) Add a `RAISE NOTICE t1 = % and delta = %', t1, delta;` right after `SELECT INTO delta ...` to see if you are actually getting the values you think you are. – Adrian Klaver Jul 05 '22 at 15:39
  • @AdrianKlaver I followed your (1) suggestion and the system is able to save the data to the second table. I noticed I made a mistake when I recreated the trigger, but now it is working fine. Thank you for the clarification – gpare Jul 07 '22 at 09:25
  • It was not my suggestion it was @Bergi, I was just noting you had not made the change. – Adrian Klaver Jul 07 '22 at 14:48

0 Answers0