Postgresql，使用触发器维护分层数据

我有邻接列表表帐户 ，列id ， 代码 ， 名称和parent_id 。 为了使排序和显示更容易，我添加了两列： 深度和路径 （物化路径）。 我知道，postgresql具有物化路径的专用数据类型，但我想使用更通用的方法，而不是特定于postgresql。 我还在我的设计中应用了几条规则：
1） 代码最多可以有10个字符
2）最大深度为9; 所以根账户最多可以有8个子账户。
3）一旦设置， parent_id永远不会改变，所以不需要将树的分支移动到树的另一部分。
4） 路径是帐户的物化路径，长达90个字符; 它是通过连接帐户代码构建的，右侧填充为10个字符; 例如，像'10000______10001______'。
因此，为了自动维护深度和路径列，我为帐户表创建了一个触发器和一个触发器函数：

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();

以上似乎适用于INSERTs。 但对于UPDATE，则会引发错误：“表'帐户'上的UPDATE语句预计会更新1行;匹配0。” 我对“UPDATE public.account ...”部分有疑问。 有人可以帮助我纠正上述触发器吗？

---

那么，在上面的代码中，更新部分会更新触发器被触发的所有记录（包括记录）（并发执行？）。 这似乎不起作用。 所以我不得不发布2个不同的陈述：

UPDATE {0}.{1} 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) 
              AND id <> NEW.id;
NEW.path = OVERLAY(OLD.path PLACING rpad(NEW.code, 10) 
                       FROM (OLD.depth - 1) * 10 + 1 FOR 10);

链接地址: http://www.djcxy.com/p/16793.html

上一篇: Postgresql, maintaining hierarchical data with triggers

下一篇: Storing Hierarchical Data (MySQL) for Referral Marketing