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:
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
