Postgresql, maintaining hierarchical data with triggers
I have adjacency list table account , with columns id , code , name , and parent_id . To make sorting and displaying easier I added two more columns: depth , and path (materialized path). I know, postgresql has a dedicated data type for materialized path, but I'd like to use a more generic approach, not specific to postgresql. I also applied several rules to my design:
1) code can be up to 10 characters long
2) Max depth is 9; so root account can have sub accounts at maximum 8 level deep.
3) Once set, parent_id is never changed, so there's no need to move a branch of tree to another part of the tree.
4) path is an account'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______'.
So, to automatically maintain depth and path columns, I created a trigger and a trigger function for the account table:
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();
The above seems to work for INSERTs. But for UPDATEs, an error is thrown: "UPDATE statement on table 'account' expected to update 1 row(s); 0 were matched.". I have a doubt on "UPDATE public.account ..." part. Can someone help me correct the above trigger?
Well, in the above code, update part updates all records, including the record, on which the trigger was fired (concurrency execption?). That seems not to work. So I had to issue 2 different statements:
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);
上一篇: 将分层数据存储在数据库中