Re: celko nested set functions -- tree move - Mailing list pgsql-sql

I think you should take a closer look at Greg's function. It is uses
lfts as parameters in the function mainly just to make the function
implementation independent; I was able to easily adapt it to my schema
which uses unique id's for each object in the tree hierarchy.

After looking your function over, I also have some concerns about moving
children to new parents with lft and rgt smaller than the child, because
the math is different depending on which way your moving in the tree.
It's possible that your use of treeid's and universe id's makes up for
this, though it's a little hard to discern without seeing the schema,
perhaps you can post schema and some test data? 

I'm also curious how many nodes you have in your tree, and at how many
levels. It seems like your function would have performance issues over
large trees since it requires 3 select statements, 3 updates, and a lock
table. Compare this with Greg's function which requires 2 selects and 1
update, with no lock. 

As a final note, you might want to rewrite your select statements like:
SELECT rgt, universeid, treeid  
FROM list_objects
WHERE objid_auto=t_newparent
INTO newparentrgt, newparentuid, newparenttid;

I think it's more readable and probably a little more efficient since
you are doing less variable assignment.

Robert Treat

On Tue, 2002-11-26 at 00:13, Martin Crundall wrote:
> I'm not sure that keying off lft is safe in a multi-user environment.  I
> opted to create and use an objid on the tree definition table, since its
> identity is static.  I also found that when trees get active, allowing for
> tree IDs increased operation speed quite a bit (i actually push this to
> two levels--a 'universe id' and then a 'tree id').  Here's my version. 
> Clearly not as elegantly written, but nothing's gone awry yet.
> 
> --
> ---------------------------------------------------------------------------
> --    Title: trackmyproject_tree_move()
> -- Function: moves a tree branch in the hierarchy from one parent to
> --           another.
> --    parms: srcobj       the branch/object to be moved
> --           newparent    the new parent for the object to be moved
> --  Returns: zero
> --
> ---------------------------------------------------------------------------