假设您按时间顺序输入数据,最好的选择可能是使用INSERT
触发器。这是因为您不仅需要插入新数据,还需要在当前条目之前关闭最近条目的日期范围。
使用日期范围的解决方案
CREATE FUNCTION trf_bi_observations() RETURNS trigger AS $$
DECLARE
recent_date date;
BEGIN
SELECT max(lower(vintage)) INTO recent_date
FROM observations;
IF lower(NEW.vintage) <= recent_date OR NOT upper_inf(NEW.vintage) THEN
RAISE NOTICE 'New vintage must be more recent than latest vintage and have an open upper bound';
RETURN NULL;
END IF;
UPDATE observations
SET vintage = daterange(recent_date, lower(NEW.vintage))
WHERE lower(vintage) = recent_date;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_bi_observations
BEFORE INSERT ON observations
FOR EACH ROW EXECUTE PROCEDURE trf_bi_observations();
请注意,使用此
INSERT
触发器后,您不再需要
EXCLUDE
约束,因为触发器函数的逻辑不允许重叠。但是,您必须考虑更新和删除。如果您想禁止在此表上进行删除操作,可以简单地撤销权限或设置一个
BEFORE DELETE
触发器,什么也不做,只需
RETURN NULL
(这将使
DELETE
操作失败)。您必须允许更新(否则上面的插入触发器将失败),但可以将其限制为更改
value
列的值和关闭最近一行的上限。
IF upper_inf(OLD.vintage) THEN
NEW.vintage = daterange(lower(OLD.vintage), upper(NEW.vintage));
ELSE
NEW.vintage = OLD.vintage
END IF;
没有日期范围的解决方案
根据您的要求 - 没有重叠,没有间隙 - 您可以通过仅使用“vintage”列的date
而不是daterange
来大大简化数据模型。该范围始终可以从当前行的date
到下一个角色的date
构建。这隐含地满足了两个要求,并且触发器函数变得更加简单:
CREATE FUNCTION trf_bi_observations2() RETURNS trigger AS $$
BEGIN
PERFORM * FROM observations WHERE vintage >= NEW.vintage;
IF FOUND THEN
RAISE NOTICE 'New vintage must be more recent than latest vintage';
RETURN NULL;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tr_bi_observations2
BEFORE INSERT ON observations
FOR EACH ROW EXECUTE PROCEDURE trf_bi_observations2();
现在你不必使用开放上界更新最近的行,因此可以禁止对“vintage”列进行任何更新。您还可以决定允许插入和更新任何日期,以便单个行的日期范围随此类事件而更改。由您决定。无论哪种情况,使用简单的
date
值比使用
daterange
更容易且更快。
您仍然可以使用正确的
SELECT
语句以
daterange
格式检索具有“vintage”的数据。
SELECT daterange(vintage, lead(vintage) OVER (ORDER BY vintage ASC)) AS vintage, value
FROM observations;
< p >
lead()
窗口函数将为您提供窗口帧中下一行的 "vintage" 列的
日期
,这是由于排序而导致的下一个日期。