postgresql - 如何应用 'advanced' 基于查询的约束？

Question

我找不到应用“高级”基于查询的约束的方法。

例如，对于以下虚构架构，我如何强制 Bob 日常事件的总百分比不超过 100%？

我已经研究过 trigger constraints但我不认为他们会做我想做的事(如果满足某些条件则中止插入/更新)。

感谢您阅读我的问题以及您可以提供的任何帮助。

create table employee (
    id serial PRIMARY KEY,
    name varchar(100) NOT NULL
);

create table work_day (
    employee_id integer references employee(id) NOT NULL,
    percentage integer NOT NULL CHECK (percentage > 0 and percentage <= 100),
    activity varchar(100) NOT NULL
);

INSERT INTO employee (name) VALUES ('bob');

-- Bob spends 50% of the day slacking, 20% eating and 30% working (total = 100%)
INSERT INTO work_day (employee_id, percentage, activity) VALUES (1, 50, 'slacking'); 
INSERT INTO work_day (employee_id, percentage, activity) VALUES (1, 20, 'eating');
INSERT INTO work_day (employee_id, percentage, activity) VALUES (1, 30, 'working');

-- This should be invalid!!! 100% of Bob's time has already been allocated
INSERT INTO work_day (employee_id, percentage, activity) VALUES (1, 10, 'invalid');

Answer 1

我已经测试了这段代码并且它有效:

CREATE OR REPLACE FUNCTION check_work_day_percentage() RETURNS trigger AS $$
    BEGIN
        IF coalesce((select sum(percentage) 
                    from work_day
                    where employee_id = NEW.employee_id
                    and activity != NEW.activity), 0) 
                + coalesce(NEW.percentage, 0) > 100 THEN
            RAISE EXCEPTION 'Employee % exceeds 100 percent', NEW.employee_id;
        END IF;
        RETURN NEW;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER work_day_trigger 
BEFORE INSERT OR UPDATE ON work_day
FOR EACH ROW EXECUTE PROCEDURE check_work_day_percentage();

仅供引用，表约束不会这样做:根据 docs ,

Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row.