我有邻接列表表 account ，列 id ， code ， name 和 parent_id . 为了使排序和显示更容易，我添加了两列： depth 和 path （物化路径） . 我知道，postgresql有物化路径的专用数据类型，但我想使用更通用的方法，而不是特定于postgresql . 我还在设计中应用了几条规则：
1） code 最长可达10个字符
2）最大深度为9;所以root帐户可以有最多8级深度的子帐户 .
3）一旦设置， parent_id 永远不会改变，因此不需要将树的分支移动到树的另一部分 .
4） path 是一个帐户's materialized path, which is up to 90 characters long; it is built by concatenating account codes, right padded to 10 characters long; for example, like ' 10000______10001______' .
因此，为了自动维护 depth 和 path 列，我为 account 表创建了一个触发器和一个触发器函数：
CREATE FUNCTION public.fn_account_set_hierarchy()
RETURNS TRIGGER AS $$
DECLARE d INTEGER; p CHARACTER VARYING;
BEGIN
IF TG_OP = 'INSERT' THEN
IF NEW.parent_id IS NULL THEN
NEW.depth := 1;
NEW.path := rpad(NEW.code, 10);
ELSE
BEGIN
SELECT depth, path INTO d, p
FROM public.account
WHERE id = NEW.parent_id;
NEW.depth := d + 1;
NEW.path := p || rpad(NEW.code, 10);
END;
END IF;
ELSE
IF NEW.code IS DISTINCT FROM OLD.code THEN
UPDATE public.account
SET path = OVERLAY(path PLACING rpad(NEW.code, 10)
FROM (OLD.depth - 1) * 10 + 1 FOR 10)
WHERE SUBSTRING(path FROM (OLD.depth - 1) * 10 + 1 FOR 10) =
rpad(OLD.code, 10);
END IF;
END IF;
RETURN NEW;
END$$
LANGUAGE plpgsql
CREATE TRIGGER tg_account_set_hierarchy
BEFORE INSERT OR UPDATE ON public.account
FOR EACH ROW
EXECUTE PROCEDURE public.fn_account_set_hierarchy();
以上似乎适用于INSERT . 但是对于UPDATE，会抛出一个错误：“表'帐户'上的UPDATE语句'预计会更新1行;匹配的是0 . ”我对“UPDATE public.account ...”部分有疑问 . 有人可以帮我纠正上面的触发器吗？
1 回答
那么，在上面的代码中，更新部件会更新触发器触发的所有记录，包括记录（并发执行？） . 这似乎不起作用 . 所以我不得不发出两个不同的陈述：