当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - If Exists Not working as I expected

问题描述:

I must be overlooking something obvious with my logic in the function below. I am trying to do a before insert, where I have a master (parent) table and a child (that inherits from master). I am trying to check if a record exists, if it does do an update, otherwise do an insert. But, my IF EXISTS is not working as I would have expected and it always results in an insert.

Function:

 CREATE OR REPLACE FUNCTION alerting.dataset_aggregations_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF EXISTS(SELECT 1 FROM alerting.dataset_aggregations dm

WHERE dm.dataset = NEW.dataset AND dm.start_date_epoch = NEW.start_date_epoch) THEN

UPDATE alerting.dataset_aggregations

SET message_count = NEW.message_count

WHERE dm.dataset = NEW.dataset

AND dm.start_date_epoch = NEW.start_date_epoch;

ELSE

INSERT INTO alerting.dataset_aggregations(dataset, start_date,start_date_epoch, message_count) VALUES(NEW.dataset, NEW.start_date, NEW.start_date_epoch,NEW.message_count);

END IF;

END;

$$

LANGUAGE plpgsql;

Trigger:

 --Before Insert Trigger

CREATE TRIGGER insert_dataset_aggregations_master

BEFORE INSERT ON alerting.dataset_aggregations_master

FOR EACH ROW EXECUTE PROCEDURE alerting.dataset_aggregations_insert_trigger();

Example Insert (Insert twice and creates two entries):

INSERT INTO alerting.dataset_aggregations(dataset,start_date,start_date_epoch,message_count) VALUES('dataset1','2016-05-17T08:00:00.000Z',1463472000000,'500');

分享给朋友:
您可能感兴趣的文章:
随机阅读: