Postgresql, maintaining hierarchical data with triggers

Question

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?

synergetic synergetic · Accepted Answer · 2014-01-21T05:04:47

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

Postgresql, maintaining hierarchical data with triggers

1 Answers