Forum OpenACS Q&A: Response to Bug in OpenACS 3.2.5 Categories

Posted by Stan Kaufman on
Well, here's an answer to my own question. Using this procedure

proc find_kids { db node_id kid_list } {

    upvar $kid_list my_kid_list

    set candidate_list [database_to_tcl_list $db "
      select child_category_id 
      from category_hierarchy
      where parent_category_id = '$node_id'"]

    foreach kid_id $candidate_list {
        lappend my_kid_list $kid_id
        find_kids $db $kid_id my_kid_list

called thus:

    #  The original children_list doesn't work correctly
    #  Find all children, grand-children, etc of category in question and
    #  store them in a list.  The category MUST NOT have parent among any
    #  element in this list.
#    set children_list [database_to_tcl_list $db "select #h.child_category_id
#    from category_hierarchy h
#    where category_hierarchy_level($category_id, h.child_category_id, #0) is not null"]

# instead use this    
set children_list [list]
find_kids $db $category_id children_list

Calling a select recursively like this is butt-ugly, but for some reason I can't figure out how to write the find_kids procedure correctly in pl/pgsql. If it's simple and obvious how to do that, thanks to anyone who can show me.