Ygthkb

Practical reasons to have both a large police force and bounty hunting network?

Can the Shape Water Cantrip be used to manipulate blood?

A peculiar integral identity

Ahoy, Ye Traveler!

Where is this quote about overcoming the impossible said in "Interstellar"?

Why is it "take a leak?"

Would the melodic leap of the opening phrase of Mozart's K545 be considered dissonant?

Difference between 'stomach' and 'uterus'

Is every open circuit a capacitor?

PTIJ: Aharon, King of Egypt

Are there other characters in the Star Wars universe who had damaged bodies and needed to wear an outfit like Darth Vader?

How to fix my table, centering of columns

PTIJ: Mordechai mourning

Deal the cards to the players

How to mitigate "bandwagon attacking" from players?

How do you say “my friend is throwing a party, do you wanna come?” in german

Is there a full canon version of Tyrion's jackass/honeycomb joke?

Transparent Materials for Creating Martian Dome Ceilings

Plagiarism of code by other PhD student

A bug in Excel? Conditional formatting for marking duplicates also highlights unique value

Is there a frame of reference in which I was born before I was conceived?

GDAL GetGeoTransform Documentation -- Is there an oversight, or what am I misunderstanding?

PTIJ: Is all laundering forbidden during the 9 days?

How does insurance birth control work?

Checking Data Boundaries And Trigger Notification

0

I'm creating backend that send notification if inserted data exceed expected value.

I have 3 (simple structure) tables:

bigFatData table:

id,locId,temp,moistLevel,salRange,insertedDate

fencing table:

id,locId,fencingFld,fencingVal,insertedDate

notifications table:

id,locId,msg,insertedDate

So for example if there's a record in fencing table:

12,101,'temp',55.20,2019-03-07 10:50:54.200294+07

and one of our sensor tries to insert something to bigFatTable, like:

101,56.00,15,100,NOW()

it will trigger another insert to notifications table, like:

101,concat('Warning! Data: ', fencingFld, ' exceeds value: ',
fencingVal), NOW()

Right now, I'm creating ugly IF EXISTS check in trigger like this:

CREATE OR REPLACE FUNCTION checkDataFencingFunction() RETURNS TRIGGER AS

$$

BEGIN

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'temp' AND fencingFld < NEW.temp::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'moistLevel' AND fencingFld < NEW.moistLevel::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'salRange' AND fencingFld < NEW.salRange::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;



END;

$$

language plpgsql;



CREATE TRIGGER checkDataFencingTrigger

     AFTER INSERT ON bigFatData

     FOR EACH ROW

     EXECUTE PROCEDURE checkDataFencingFunction();

I believe there's a better way than putting tons of IF EXISTS check in the function. Any suggestions?

Thanks!

postgresql trigger

share

asked 2 mins ago

SanShinSanShin

1

New contributor

SanShin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

I'm creating backend that send notification if inserted data exceed expected value.

I have 3 (simple structure) tables:

bigFatData table:

id,locId,temp,moistLevel,salRange,insertedDate

fencing table:

id,locId,fencingFld,fencingVal,insertedDate

notifications table:

id,locId,msg,insertedDate

So for example if there's a record in fencing table:

12,101,'temp',55.20,2019-03-07 10:50:54.200294+07

and one of our sensor tries to insert something to bigFatTable, like:

101,56.00,15,100,NOW()

it will trigger another insert to notifications table, like:

101,concat('Warning! Data: ', fencingFld, ' exceeds value: ',
fencingVal), NOW()

Right now, I'm creating ugly IF EXISTS check in trigger like this:

CREATE OR REPLACE FUNCTION checkDataFencingFunction() RETURNS TRIGGER AS

$$

BEGIN

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'temp' AND fencingFld < NEW.temp::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'moistLevel' AND fencingFld < NEW.moistLevel::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'salRange' AND fencingFld < NEW.salRange::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;



END;

$$

language plpgsql;



CREATE TRIGGER checkDataFencingTrigger

     AFTER INSERT ON bigFatData

     FOR EACH ROW

     EXECUTE PROCEDURE checkDataFencingFunction();

I believe there's a better way than putting tons of IF EXISTS check in the function. Any suggestions?

Thanks!

postgresql trigger

share

asked 2 mins ago

SanShinSanShin

1

New contributor

SanShin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I'm creating backend that send notification if inserted data exceed expected value.

I have 3 (simple structure) tables:

bigFatData table:

id,locId,temp,moistLevel,salRange,insertedDate

fencing table:

id,locId,fencingFld,fencingVal,insertedDate

notifications table:

id,locId,msg,insertedDate

So for example if there's a record in fencing table:

12,101,'temp',55.20,2019-03-07 10:50:54.200294+07

and one of our sensor tries to insert something to bigFatTable, like:

101,56.00,15,100,NOW()

it will trigger another insert to notifications table, like:

101,concat('Warning! Data: ', fencingFld, ' exceeds value: ',
fencingVal), NOW()

Right now, I'm creating ugly IF EXISTS check in trigger like this:

CREATE OR REPLACE FUNCTION checkDataFencingFunction() RETURNS TRIGGER AS

$$

BEGIN

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'temp' AND fencingFld < NEW.temp::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'moistLevel' AND fencingFld < NEW.moistLevel::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'salRange' AND fencingFld < NEW.salRange::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;



END;

$$

language plpgsql;



CREATE TRIGGER checkDataFencingTrigger

     AFTER INSERT ON bigFatData

     FOR EACH ROW

     EXECUTE PROCEDURE checkDataFencingFunction();

I believe there's a better way than putting tons of IF EXISTS check in the function. Any suggestions?

Thanks!

postgresql trigger

share

asked 2 mins ago

SanShinSanShin

1

New contributor

SanShin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

CREATE OR REPLACE FUNCTION checkDataFencingFunction() RETURNS TRIGGER AS

$$

BEGIN

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'temp' AND fencingFld < NEW.temp::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'moistLevel' AND fencingFld < NEW.moistLevel::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;

        IF EXISTS (SELECT locId,fencingFld,fencingVal FROM fencing WHERE locId = NEW.locID AND fencingFld = 'salRange' AND fencingFld < NEW.salRange::double precision) THEN

        INSERT INTO data.notifications(locId, msg) VALUES (NEW.locID, 'Warning! Data exceeds!');

END IF;



END;

$$

language plpgsql;



CREATE TRIGGER checkDataFencingTrigger

     AFTER INSERT ON bigFatData

     FOR EACH ROW

     EXECUTE PROCEDURE checkDataFencingFunction();

share

asked 2 mins ago

SanShinSanShin

1

New contributor

SanShin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 2 mins ago

SanShinSanShin

1

New contributor

SanShin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 mins ago

SanShinSanShin

1

New contributor

SanShin is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 mins ago

SanShinSanShin

1