From: | "Martin Crundall" <pgsql(at)ac6rm(dot)net> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | celko nested set functions -- tree move |
Date: | 2002-11-26 05:13:04 |
Message-ID: | 63745.24.52.245.104.1038287584.squirrel@webmail.ac6rm.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 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:
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-11-26 05:29:29 | Re: subselect instead of a view... |
Previous Message | Dan Langille | 2002-11-26 05:07:29 | Re: subselect instead of a view... |