Thread: reversion? Recursion question
Hello, For lack of a better title, I need to a reverse recursion. Where I have the parent ID, but then I need to find all it's children, grandchildren, great-grandchildern, etc for the parent-id. I guess this would be like a directory and all its sub-directories. I modified a normal recursive function I got from the cookbook , but it's impossible for me to decipher where the top level begins and the children end. Could someone point me in the right direction to clean this up? I can provide sample data if needed. TIA Patrick Hatcher -Table CREATE TABLE mdc_category ( keyp_category int4 NOT NULL, category_name varchar(255), parent_keypcategoryid int4, CONSTRAINTxpkmdc_category UNIQUE (keyp_category) ) CREATE FUNCTION category_descriptiontest(int4) RETURNS varchar AS ' DECLARE v_category_id ALIAS FOR $1; DECLARE tmp_record RECORD; DECLARE tmp_id VARCHAR; DECLARE tmp_code VARCHAR; DECLARE keycheck INT4; BEGIN tmp_code:=''''; FOR tmp_record IN SELECT keyp_category,category_name,parent_keypcategoryid from mdc_category where parent_keypcategoryid = v_category_id LOOP IF tmp_record.parent_keypcategoryid=0 THEN RETURN tmp_record.keyp_category; END IF; tmp_id:=category_descriptiontest(tmp_record.keyp_category); IF tmp_record.keyp_category<>0 THEN tmp_code:= tmp_code|| '' -'' || tmp_id::varchar || tmp_record.keyp_category::varchar || '' -''; /* tmp_code:= tmp_code || '''' || tmp_id::varchar || '' -'' || tmp_record.keyp_category::varchar; */ END IF; END LOOP; RETURN tmp_code;END; ' LANGUAGE 'plpgsql';
On Tue, 2003-04-15 at 15:41, Patrick Hatcher wrote: > Hello, > For lack of a better title, I need to a reverse recursion. Where I have > the parent ID, but then I need to find all it's children, grandchildren, > great-grandchildern, etc for the parent-id. I guess this would be like a > directory and all its sub-directories. You may want to look at another way of doing this. Joe Celko gives an example of "nested sets" which allows you to select tree-like sets from your database very efficiently. His book "SQL for Smarties" has a chapter on this, or google for "celko sql tree" and you'll find some articles he's written on it. Here's a link to one: http://www.intelligententerprise.com/001020/celko.shtml. I just converted a project from the approach you outlined to the nested sets approach, and it has really helped. The general idea is to think of your tree as a bunch of ovals which fall inside or next to one another. I'll let you read his article - he explains things fairly well. The problem with your recursive query approach is that you'll have to do one query per item in your tree which quickly gets out of hand. With the nested sets approach you can get everything in one simple query. It also allows you to do nice things like find all ancestors of a given node (parent, grandparent, great-grandparent, etc), or find all leaf-nodes (items that have no children). The only tricky part can be writing the appropriate triggers to insert, remove, and move items in your tree. I hope this helps, -Brad
Brad Hilton wrote : > On Tue, 2003-04-15 at 15:41, Patrick Hatcher wrote: > > Hello, > > For lack of a better title, I need to a reverse recursion. Where I have > > the parent ID, but then I need to find all it's children, grandchildren, > > great-grandchildern, etc for the parent-id. I guess this would be like a > > directory and all its sub-directories. > > You may want to look at another way of doing this. Joe Celko gives an > example of "nested sets" which allows you to select tree-like sets from > your database very efficiently. His book "SQL for Smarties" has a > chapter on this, or google for "celko sql tree" and you'll find some > articles he's written on it. Here's a link to one: > http://www.intelligententerprise.com/001020/celko.shtml. Thanks for that! It's going to improve my life no end! David. -- David Goodwin [ dof at codepoets dot co dot uk ] [ http://www.codepoets.co.uk ]