pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcelko nested set functions
3+ messages / 3 participants
[nested] [flat]
* celko nested set functions
@ 2002-10-02 19:48 Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 1 reply; 3+ messages in thread
From: Robert Treat @ 2002-10-02 19:48 UTC (permalink / raw)
To: pgsql-sql
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,
Robert Treat
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: celko nested set functions
@ 2002-10-02 22:11 Josh Berkus <josh@agliodbs.com>
parent: Robert Treat <xzilla@users.sourceforge.net>
0 siblings, 0 replies; 3+ messages in thread
From: Josh Berkus @ 2002-10-02 22:11 UTC (permalink / raw)
To: Robert Treat <xzilla@users.sourceforge.net>; pgsql-sql
Robert,
> 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,
I've been planning on doing this as an article for a while. I'll send you an
answer off-list because I don't want to publicize it until I can add some
commentary.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: celko nested set functions
@ 2002-11-25 20:22 greg@turnstep.com
0 siblings, 0 replies; 3+ messages in thread
From: greg@turnstep.com @ 2002-11-25 20:22 UTC (permalink / raw)
To: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-11-25 20:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-02 19:48 celko nested set functions Robert Treat <xzilla@users.sourceforge.net>
2002-10-02 22:11 ` Josh Berkus <josh@agliodbs.com>
2002-11-25 20:22 Re: celko nested set functions greg@turnstep.com
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