Re: Joe Celko Function : problem

Ben-Nes,

One problem with the nested set approach is that you have to make sure that the
root set of
each hierarchy has upper and lower bounds that do not overlap with those of any
other
root set. So, how to do that? Attached is sql that creates a table that tracks
what value
ranges have been used. It also allows one to return a range.

What I have yet to do is write the code that "reallocates" a root set's range,
which
would require getting all child nodes and adjusting their bounds .... but this
is not really
that hard since it is just adding/subtracting values to all their bounds.

Also included is a JUnit test class for testing the PL/pgsql procedures. One
test randomly
gets ranges of size <= int (the table type is bigint) and then randomly returns
the ranges
making sure all of the rows coalesce back to a single row.

Richard