From: greg@turnstep.com
To: pgsql-sql@postgresql.org
Subject: Re: celko nested set functions
Date: Mon, 25 Nov 2002 20:22:34 -0000
Message-ID: <479891932701b5829f621f49d602a69a@biglumber.com> (raw)
-----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 BETWEEN cleft AND cright THEN lft + treeshift
ELSE lft END,
rgt = CASE
WHEN rgt BETWEEN leftbound AND rightbound THEN rgt + cwidth
WHEN rgt BETWEEN cleft AND cright THEN rgt + treeshift
ELSE rgt 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-----
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: greg@turnstep.com
Subject: Re: celko nested set functions
In-Reply-To: <479891932701b5829f621f49d602a69a@biglumber.com>
* 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