Forum OpenACS Q&A: connect by patch?

This is fairly trivial, the only thing we have to do is to locate the parent of newly inserted record, if such exists.

create function nested_set_insert_node () returns opaque as '
declare
    rightmost_node    acs_object_types.r_node%TYPE;
    parent_r_node     acs_object_types.r_node%TYPE;
begin
    --  We differentiate two cases, depending on whether the newly
    --  inserted has parent or not.
    if new.supertype is null then
        --  New node is added at the end of the nested set list.
        select coalesce(max(r_node),0) into rightmost_node from acs_object_types;
        new.l_node := rightmost_node + 1;
        new.r_node := rightmost_node + 2;
    else
        --  New node is inserted at the right node of the parent,
        --  shifting everything two places to the right.
        select r_node into parent_r_node from acs_object_types
          where object_type = new.supertype;
        update acs_object_types
          set l_node = l_node + 2
          where l_node > parent_r_node;
        update acs_object_types
          set r_node = r_node + 2
          where r_node >= parent_r_node;
        new.l_node := parent_r_node;
        new.r_node := parent_r_node + 1;
    end if;
    return new;
end;
' language 'plpgsql';

create trigger nested_set_insert_tr before insert
  on acs_object_types for each row
  execute procedure nested_set_insert_node() ;

UPDATE is slightly complicated. I don't know why BEFORE trigger did not produce correct results, so I switched to AFTER trigger which rectified the problem. One interesting bit is that you cannot use new.l_node and new.r_node since they can be obsoleted by UPDATE statements issued by the trigger itself. Anyway, I didn't optimize anything so it might be worth tuning this little bit to reduce the amount of UPDATEs.

create function nested_set_update_node () returns opaque as '
declare
    rightmost_node    acs_object_types.r_node%TYPE;
    nested_set_width  acs_object_types.r_node%TYPE;
    shift_offset      acs_object_types.r_node%TYPE;
    parent_row        acs_object_types%ROWTYPE;
    this_row          acs_object_types%ROWTYPE;
   
begin
    --  We cannot use new.l_node and new.r_node since they can
    --  be superseded by bunch of UPDATEs in this very trigger
    --  (this happens if the trigger fires on more than 1 row).
    --  So we retrieve the actual value of l_node and r_node with this:
    select * into this_row
        from acs_object_types
        where object_type = new.object_type;
    --  We have to rearrange nodes only if supertype has been changed.
    --  This leads to 3 cases:
    --    1.  supertype changes from NOT NULL to NULL
    --    2.  supertype changes from NULL to something NOT NULL
    --    3.  supertype changes from one NOT NULL value to another
    if old.supertype = new.supertype then
        return new;
    end if;
    if old.supertype is null and new.supertype is null then
        return new;
    end if;
    --  In any case we must shift entire subset out of its parent
    --  and put it to the right of the rightmost node, to prevent
    --  it from being overwritten by node adjustments.
    select max(r_node) into rightmost_node from acs_object_types;
    nested_set_width := this_row.r_node - this_row.l_node + 1;
    shift_offset := rightmost_node + 1 - this_row.l_node;
    --  Shift nested subset out of its parent.
    update acs_object_types
        set l_node = l_node + shift_offset,
            r_node = r_node + shift_offset
        where l_node >= this_row.l_node
          and r_node <= this_row.r_node;
    --  It''s good to know that the [this_row.l_node,this_row.r_node] interval
    --  is now not occupied by nodes.
    --
    --  Case 1)
    --
    if old.supertype is not null and new.supertype is null then
        --  Since we have already lifted our subset out of its
        --  position, we will simply renumber the nodes to fill the gaps.
        update acs_object_types
            set l_node = l_node - nested_set_width
            where l_node > this_row.r_node;
        update acs_object_types
            set r_node = r_node - nested_set_width
            where r_node > this_row.r_node;
        return new;
    end if;
    --
    --  Case 2) and 3)
    --
    if new.supertype is not null then
        --  The tricky part here is that we must pay attention whether
        --  the gap is to the left or to the right of inserting point.
        select * into parent_row
            from acs_object_types
            where object_type = new.supertype;
            parent_row.l_node, parent_row.r_node;
        --  Where will this subset be moved?
        if parent_row.r_node < this_row.l_node then
            --  Gap is to the right of inserting point
            update acs_object_types set
                l_node = l_node + nested_set_width
                where l_node > parent_row.r_node
                and l_node < this_row.l_node ;
            update acs_object_types set
                r_node = r_node + nested_set_width
                where r_node >= parent_row.r_node
                and r_node < this_row.l_node ;
            --  Place the subset under its new parent
            shift_offset := rightmost_node + 1 - parent_row.r_node;
        else
            --  The gap is to the LEFT of inserting point
            update acs_object_types set
                l_node = l_node - nested_set_width
                where l_node <= parent_row.l_node
                and l_node > this_row.r_node ;
            update acs_object_types set
                r_node = r_node - nested_set_width
                where r_node < parent_row.l_node
                and r_node > this_row.r_node ;
            shift_offset := rightmost_node + nested_set_width - parent_row.l_node;      
        end if;
        update acs_object_types set
            r_node = r_node - shift_offset,
            l_node = l_node - shift_offset
            where l_node > rightmost_node ;
        return new;
    end if;
    return new;
end;
' language 'plpgsql';

create trigger nested_set_update_tr after update
  on acs_object_types for each row
  execute procedure nested_set_update_node() ;