Postgresql - zaawansowana funkcja?

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:

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

Problem w tym, że w tej specyfikacji powyżej nie pada żadne pytanie. W związku z tym to wygląda na zlecenie, ale w takim wypadku brakuje informacji ile płaci...

Widząc że OP nie popisał się i post napisał w taki sposób, jakby czekał na gotowe rozwiązanie – wątek przenoszę tutaj.