-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
NotDashEscaped: You need GnuPG to verify this message
> I'm wondering if anyone has written code that enables you to move
> entities between parents in a nested set model. Specifically something
> that can do it without deleting any of the children involved in the
> process. I looked in the postgresql cookbook which had adding and
> building tree's, but not moving. I'm hoping to find something
> postgresql specific but if not that's ok. Thanks in advance,
CREATE FUNCTION MoveTree (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;
BEGIN
SELECT lft, rht FROM tree WHERE lft = $1 INTO cleft, cright;
SELECT lft, rht FROM tree WHERE lft = $2 INTO pleft, pright;
-- Make sure the child exists
IF cleft IS NULL THEN
RETURN ''No entry found with a 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;
-- Self-move makes no sense
IF cleft = pleft THEN
RETURN ''Cannot move: entries are identical'';
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;
ELSE
treeshift := pleft - cright;
leftbound := cright + 1;
rightbound := pleft;
cwidth := cleft-cright-1;
END IF;
UPDATE tree
SET lft = CASE
WHEN lft BETWEEN leftbound AND rightbound THEN lft + cwidth
WHEN lft BETWEEN cleft AND cright THEN lft + treeshift
ELSE lft END,
rht = CASE
WHEN rht BETWEEN leftbound AND rightbound THEN rht + cwidth
WHEN rht BETWEEN cleft AND cright THEN rht + treeshift
ELSE rht END;
RETURN ''Tree has been moved'';
END;
'
LANGUAGE 'plpgsql';
Greg Sabino Mullane [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200210291424
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE9vuDNvJuQZxSWSsgRApruAJ0bD2XyonsYNHV+XVEBYqJji3jxygCfeVk/
27Cl7rTs5bQAkyBQXuXl3mw=
=MZbR
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
