I need a help with some postgresql function. We have got system that register some values that are provided by external measure system.Every 5 sec new values are added to value_tab. We have to add alerts and mark entries with db that need to be sent to user (notification system).

Lets consider we have 4 tables:

param_tab ---------- param_id (serial) name (varchar)  value_tab --------- value_id (serial) param_id (integer) measured_value (double) measure_date (timestamp)  alert_tab ---------- alert_id (serial) param_id (integer) start_value (double) stop_value (double) opened (smallint) - default 0 opened_value (double) closed (smallint) - default 0 closed_value (double) last_check_date (timestamp) need_action (smallint) - default 0  target_tab ---------- target_id (serial) alert_id (integer) margin_value (double) closed (smallint) - default 0 closed_value (double) need_action (smallint) - default 0 

In value_tab we are putting values that are provided by external measure system.

I need function (or functions) that:

1. check_if_need_to_open(timestamp check_date) that function will take timestamp attribute and will take all not opened alerts from alert_tab (alert_tab.opened = 0) and take all value_tab.measured_value where alert_tab.last_check_date <= value_tab.measure_date <= check_date. For that values function should check if value_tab.measure_value >= alert_tab.start_value. If yes then: alert item should be set like this: alert_tab.opened = 1 alert_tab.opened_value = value_tab.measured_value (form that value that trigger that rule) alert_tab.last_check_date = check_date alert_tab.need_action = 1

2. check_if_need_to_close (timestamp check_date) that function will take timestamp attribute and will take all opened alerts from alert_tab (alert_tab.opened = 1 and alert_tab.closed = 0) and take all value_tab.measured_value where alert_tab.last_check_date <= value_tab.measure_date <= check_date. For that values function should check if value_tab.measure_value <= alert_tab.stop_value. If yes then: alert item should be set like this: alert_tab.closed = 1 alert_tab.close_value = value_tab.measured_value (form that value that trigger that rule) alert_tab.last_check_date = check_date alert_tab.need_action = 1

3. check_if_target_reached(timestamp check_date) that function will take timestamp attribute and will take all opened alerts from alert_tab (alert_tab.opened = 1 and alert_tab.closed = 0) and check it’s targets (target_tab.closed = 0) and take all value_tab.measured_value where alert_tab.last_check_date <= value_tab.measure_date <= check_date. For that values function should check if value_tab.measure_value >= target_tab.margin_value. If yes then: alert item should be set like this: alert_tab.last_check_date = check_date target_tab.need_action = 1 target_tab.closed = 1 target_tab.closed_value = value_tab.measured_value (form that value that trigger that rule)

Not sure how that should be done, it’s to complicated for me but I think that psql function should be faster then parsing that all in java.

thanks for advice