Re: celko nested set functions
От | greg@turnstep.com |
---|---|
Тема | Re: celko nested set functions |
Дата | |
Msg-id | 479891932701b5829f621f49d602a69a@biglumber.com обсуждение исходный текст |
Ответ на | celko nested set functions (Robert Treat <xzilla@users.sourceforge.net>) |
Список | 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 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-----
В списке pgsql-sql по дате отправления: