Postgresql function for detecting alerts

Question

need help with PGSQL function for detecting temperature alerts. Let's imagine we have tables

**measurment_tab**
id:int
room_id:int
measurment_date:timestamp
value:long

**alert_tab**
id:int
room_id:int
alert_value:long
date_add:timestamp
last_check_date:timestamp
alert_type:varchar [two possible values: below, over]

Last measurement value is:

10|2|2020-01-02 15:33:00|11.3

and alert value:

33|2|12.7|2020-01-02 16:00:00|null|over

In this case function should detect alert when:

1. measurement value is over 12.7
2. each check I believe should updated last_check_date field to current value (now()) to limit the collection of values to check

I think this scenario is quite simple. Next one is more complicated.

Last measurement value is:

10|2|2020-01-02 15:33:00|13.3

and alert value:

33|2|12.7|2020-01-02 16:00:00|null|over

As You can see current measurement value is over alert value. In this case alert shouldn't be triggered. Alert should be triggered when:

1. measurement value goes below alert value
2. after that measurement goes again over alert value

Thanks for advice.

Answer 1

If I'm understanding the question correctly?

I would run the alert code in a Insert Trigger, and I would add a column to measurement_tab to track which alerts matched the measurement.

alter table measurement_tab add column trigger_alert_id integer[];

CREATE TRIGGER alert
BEFORE INSERT
ON measurement_tab
FOR EACH ROW
EXECUTE PROCEDURE _alert();

CREATE FUNCTION _alert()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$

declare _r record;

begin

for _r in select id from alert_tab 
        where alert_tab.room_id = measurement_tab.room_id
        and value < alert_value;
     new.trigger_alert_id = array_append(new.trigger_alert_id, _r.id);
end loop;

for _r in select id from alert_tab 
        where alert_tab.room_id = measurement_tab.room_id
        and value > alert_value;
     new.trigger_alert_id = array_append(new.trigger_alert_id, _r.id);
end loop;

Run_alert_function(new.trigger_alert_id);  
         # this is where the other alert code 
         # that emails/sends out the alerts gets fired off; 
return new;
end;

$BODY$

If there is need to block alerts that have already been triggered. Meaning once alert has been set it must be reset before it can be fired off again, this can be done with additional logic in the loop, that looks for previous entries in measurement_tab that set this alert previously.