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.
