Re: celko nested set functions

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message


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



CREATE or REPLACE FUNCTION move_tree (integer, integer) RETURNS text
AS '

-- Moves part of a nested set tree to another part.
-- Pass in the left of the child (from) and the left of the parent (to)

DECLARE

cleft INTEGER;     cright INTEGER;
pleft INTEGER;     pright INTEGER;
leftbound INTEGER; rightbound INTEGER;
treeshift INTEGER; cwidth INTEGER;
leftrange INTEGER; rightrange INTEGER;

BEGIN

-- Self-move makes no sense
IF $1 = $2 THEN RETURN ''Cannot move: entries are identical'';
END IF;

SELECT lft, rgt FROM tree WHERE lft = $1 INTO cleft, cright;
SELECT lft, rgt FROM tree WHERE lft = $2 INTO pleft, pright;

-- Make sure the child exists
IF cleft IS NULL THEN RETURN ''No entry found with an left of ''||$1;
END IF;

-- Make sure the parent exists
IF pleft IS NULL THEN RETURN ''No entry found with a left of ''||$2;
END IF;

-- Parent cannot be underneath the child
IF pleft BETWEEN cleft AND cright THEN RETURN ''Cannot move: first entry contains second'';
END IF;

-- Child may already be in the proper place
IF cleft = pleft+1 THEN RETURN ''No changes need to be made'';
END IF;

IF cleft > pleft THEN  treeshift  := pleft - cleft + 1; leftbound  := pleft+1; rightbound := cleft-1; cwidth     :=
cright-cleft+1;leftrange := cright; rightrange  := pleft;
 
ELSE treeshift  := pleft - cright; leftbound  := cright + 1; rightbound := pleft; cwidth     := cleft-cright-1;
leftrange := pleft+1; rightrange := cleft;
 
END IF;


UPDATE tree SET lft = CASE           WHEN lft BETWEEN leftbound AND rightbound THEN lft + cwidth           WHEN lft
BETWEENcleft AND cright THEN lft + treeshift           ELSE lft END,     rgt = CASE           WHEN rgt BETWEEN
leftboundAND rightbound THEN rgt + cwidth           WHEN rgt BETWEEN cleft AND cright THEN rgt + treeshift
ELSErgt END
 
WHERE lft < leftrange OR rgt > rightrange;

RETURN ''Tree has been moved'';

END;
'
LANGUAGE 'plpgsql';




Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200211251526

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE94ojRvJuQZxSWSsgRAkkUAJ0eX9VJtXYajAo60UeKYaXH1xxmkwCeJDtX
qrX7tgXmUCJNd/fphjGi7tI=
=+ADv
-----END PGP SIGNATURE-----