Re: Stuck Up In My Own Category Tree - Mailing list pgsql-sql

Hi all,

Note: I'm happy to read howtos, tutorials, archived messages - I just haven't found anything that addresses this yet.  I found a related topic on the novice list, but my post got no response there.  I've been struggling with this for about a week now and need to figure out a solution.  Heck, this may not even be the best approach to hierarchical structures, but it sure seemed reasonable when I first read up on the subject.  Anyway...

I created a category table like so (I got the idea from a website somewhere that used it in a different way, and did not discuss much about addressing what I want to accomplish):
cat_id(serial)  |  cat_name(varchar)  |  parent_id(int)  |  lineage(varchar)  |  deep(int)
1                         root_cat_a                 Null                   1                          1
2                         sub_cat_1                 1                       1-2                       2
3                         sub_sub_cat_a          2                      1-2-3                     3

I use this to categorize transactions, and use the most appropriate subcategory for any given transation item in a table called trans_details.  I can easily show transaction amounts by sub-category  (SELECT cat_name, sum(amount) AS "amount" FROM category, trans_details WHERE category_cat_id = trans_details.cat_id):

cat_name                             |    amount

Transportation: Auto: Fuel     |      $100
Transportation: Auto: Maint    |      $150
Transportation: Fares: Bus    |      $40

but what I cannot figure out is how to create a summary where I show
cat_name                    |    amount
Transportation: Auto     |      $250

or, what I *really* want:
cat_name            |    amount
Transportation     |      $290


Can anyone help me work through this?  Frankly, I'm not even sure where to begin to solve the problem.  I have been trying the WITH RECURSIVE feature, but I do not understand very well how to apply it.  The example query I have brings up an empty result set and I don't have a clue how I could modify it to make it work.  I'm not even sure it's the best method, but it seems like a reasonable approach.  I thought about using regexp to try and match the initial part of the lineage to the category_id of the parents, something like:
WHERE c1.category_id = regexp_matches(c2.lineage, 'nnn-'), but lineage is a text type column, rather than an int, and would need to be cast.

One of the problems I encounter is that a root category with no sub-categories (naturally) won't show up in the category_id = parent_id matches, since such a category has no children.