Comment
Above in this comment thread:
Trees in SQL: Nested Sets and Materialized Path » Incorrect return in sibling_number() ?
|
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today. |
|
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience. Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements? Download Free Report! |
Sponsored by:
Links to external sites are subject to change; DBAzine.com and BMC Software do not control or endorse the content of these external web sites, and are not responsible for their content. LEGAL STATEMENT © 2007 DBAzine.com. All Rights Reserved. |
CREATE or replace
function sibling_number
( numer integer, denom integer )
RETURN integer IS
ret_num integer;
ret_den integer;
ret integer;
BEGIN
if numer=1 then
return NULL;
end if;
ret_num := (numer-1)/2;
ret_den := denom/2;
ret := 1;
while floor((ret_num-1)/4) = (ret_num-1)/4 loop
if ret_num=1 and ret_den=1 then
return ret;
end if;
ret_num := (ret_num+1)/2;
ret_den := ret_den/2;
ret := ret + 1;
end loop;
RETURN ret;
END;
Also check up Postgres implementation
http://www.grzm.com/fornow/archives/2004/07/10/static_hierarchies
It might be cleaner as far as bugs are concerned