0

How to make notification when some conditions written by user are true.

I have one table DATA which has 3 rows.

name  value
----  -----
a1    10
a2    20
a3    30 

This table changes some values every 1-10 minutes.

Example: a1=12 , a2=21

ALERT: if a1>11 and a2>20 send me some message.

Question: How to check when a value in DATA fulfils the conditions in alert, and then my php/python environment takes any action on this alarm?

My idea:

  • After update on DATA table run trigger that will check if ALERT exist and set active on it (how to check when DATA id a1=12 and then check if there is some alert like a1>11)
  • Run every 1 minute process that checks if ALERT is active and execute it.

I don't now where to start...

I will use: PostgreSQL, php, cron

0

You have multiple choices here.

  • writing a crontab that will check every minutes your data (that's what you propose).
  • using NOTIFY/LISTEN with trigger like this :
CREATE OR REPLACE FUNCTION f_check_data_value()
  RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT pg_notify('data', NEW.value);
END;
$$;

CREATE TRIGGER check_data_values_trigger
AFTER INSERT OR UPDATE
  ON mytable
FOR EACH ROW EXECUTE PROCEDURE f_check_data_value();

You'll then have to write a listener (in PHP if it's what you are using). I haven't used php inn a while but the php.net documentation is a good place to start I think : http://php.net/manual/fr/function.pg-get-notify.php

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.