celko nested set functions -- tree move - Mailing list pgsql-sql

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
ALIASFOR $2;   srcspan INT4;   srclft INT4;   srcrgt INT4;   srcuid INT4;   srctid INT4;   newparentrgt INT4;
newparentuidINT4;   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
WHEREobjid_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: