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?

7
  • 2
    delta = null should be delta IS NULL Commented Jul 1, 2022 at 15:54
  • 1
    Define not working? Commented Jul 1, 2022 at 15:55
  • @Bergi Thank you for the suggestion, I changed the function code Commented Jul 5, 2022 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. Commented Jul 5, 2022 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. Commented Jul 5, 2022 at 15:39

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.