Hej,
znajomy poprosił mnie czy nie znam kogoś kto może znać odpowiedz na to pytanie :). Pomyślałem o tym forum.
Link do pytania na stacku: https://dba.stackexchange.com/questions/196406/postgresql-advance-function
Jeszcze wklejam treść pytania:
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:
-
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 -
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 -
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