From: Martin Crundall <pgsql@ac6rm.net>
To: pgsql-sql@postgresql.org
Subject: celko nested set functions -- tree move
Date: Mon, 25 Nov 2002 21:13:04 -0800 (PST)
Message-ID: <63745.24.52.245.104.1038287584.squirrel@webmail.ac6rm.net> (raw)
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:
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: pgsql@ac6rm.net
Subject: Re: celko nested set functions -- tree move
In-Reply-To: <63745.24.52.245.104.1038287584.squirrel@webmail.ac6rm.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox