Message-ID: <63745.24.52.245.104.1038287584.squirrel@webmail.ac6rm.net>
Date: Mon, 25 Nov 2002 21:13:04 -0800 (PST)
Subject: celko nested set functions -- tree move
From: "Martin Crundall" <pgsql@ac6rm.net>
To: <pgsql-sql@postgresql.org>
Importance: Normal
Reply-To: pgsql@ac6rm.net
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

I'm not sure that keying off lft is safe in a multi-user environment.  I
opted to create and use an objid on the tree definition table, since its
identity is static.  I also found that when trees get active, allowing for
tree IDs increased operation speed quite a bit (i actually push this to
two levels--a 'universe id' and then a 'tree id').  Here's my version. 
Clearly not as elegantly written, but nothing's gone awry yet.

--
---------------------------------------------------------------------------
--    Title: trackmyproject_tree_move()
-- Function: moves a tree branch in the hierarchy from one parent to
--           another.
--    parms: srcobj       the branch/object to be moved
--           newparent    the new parent for the object to be moved
--  Returns: zero
--
---------------------------------------------------------------------------
CREATE FUNCTION trackmyproject_tree_move( INT4, INT4 )
  RETURNS INT4 AS '
  DECLARE
    t_srcobj ALIAS FOR $1;
    t_newparent ALIAS FOR $2;
    srcspan INT4;
    srclft INT4;
    srcrgt INT4;
    srcuid INT4;
    srctid INT4;
    newparentrgt INT4;
    newparentuid INT4;
    newparenttid INT4;
    moveoffset INT4;
    myrec RECORD;
  BEGIN

    -- get src span info (distance between lft and rgt plus one)
    SELECT ((rgt - lft) + 1) INTO srcspan FROM list_objects
      WHERE objid_auto=t_srcobj;

    LOCK TABLE list_objects;

    -- find out where the new parent currently ends
    SELECT rgt, universeid, treeid INTO myrec FROM list_objects
      WHERE objid_auto=t_newparent;

    newparentrgt := myrec.rgt;
    newparentuid := myrec.universeid;
    newparenttid := myrec.treeid;

    -- create the gap at the bottom of the hierarchy for the
    -- new parent big enuf for the source object and its tree
    UPDATE list_objects
      SET lft = CASE WHEN lft > newparentrgt
          THEN lft + srcspan
          ELSE lft END,
        rgt = CASE WHEN rgt >= newparentrgt
          THEN rgt + srcspan
          ELSE rgt END
      WHERE rgt >= newparentrgt AND
        universeid=newparentuid AND
        treeid=newparenttid;

    -- move the object tree in to the newly created gap
    -- (may seem like a repetative select, but the above UPDATE
    -- MAY have moved the source object)
    SELECT lft, rgt, universeid, treeid INTO myrec FROM list_objects
      WHERE objid_auto=t_srcobj;
    srclft := myrec.lft;
    srcrgt := myrec.rgt;
    srcuid := myrec.universeid;
    srctid := myrec.treeid;

    -- this works even if we are jumping trees or moving up or down within
    -- the same tree
    moveoffset := srclft - newparentrgt;
    UPDATE list_objects
      SET lft = lft - moveoffset,
        rgt = rgt - moveoffset,
        universeid = newparentuid,
        treeid = newparenttid
      WHERE lft >= srclft AND rgt <= srcrgt AND
        universeid=srcuid AND
        treeid=srctid;

    -- close the gap where the source object was
    UPDATE list_objects
      SET lft = CASE WHEN lft > srclft
          THEN lft - srcspan
          ELSE lft END,
        rgt = CASE WHEN rgt > srclft
          THEN rgt - srcspan
          ELSE rgt END
      WHERE rgt >= srclft AND
        universeid=srcuid AND
        treeid=srctid;

    RETURN 0;

END;
' LANGUAGE 'plpgsql';


> Robert Treat and I came up with a better way to move
> nodes from one branch to another inside of a nested tree: