Sfrgttk

PTIJ: Aharon, King of Egypt

is 'sed' thread safe

Did Amazon pay $0 in taxes last year?

It doesn't matter the side you see it

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

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

Where is the fallacy here?

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

Book about a time-travel war fought by computers

The need of reserving one's ability in job interviews

Is every open circuit a capacitor?

How do we objectively assess if a dialogue sounds unnatural or cringy?

When was drinking water recognized as crucial in marathon running?

PTIJ: Mordechai mourning

How does signal strength relate to bandwidth?

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

Deal the cards to the players

What can I do if someone tampers with my SSH public key?

I encountered my boss during an on-site interview at another company. Should I bring it up when seeing him next time?

Draw bounding region by list of points

Can the Shape Water Cantrip be used to manipulate blood?

Has Wakanda ever accepted refugees?

Can an earth elemental drown/bury its opponent underground using earth glide?

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

Checking Data Boundaries And Trigger Notification

Why doesn't INSERT AFTER Trigger add all results?Trigger in combination with transactionCreate Trigger MySql update or insert in another tableHow to commit transaction on an after update event trigger?Postgresql trigger update whole table after deleteTrigger on temp tablesql azure: trigger error when inserting into temp table from inserted tableProblems with insert trigger and primary keyPostgresql: Insert trigger function from select query, plus static values pulled from the new.<record>How to access data inside a “before insert trigger” for every statement

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 5 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 5 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 5 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 5 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 5 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 5 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 5 mins ago

SanShinSanShin

1