Stuck Up In My Category Tree - Mailing list pgsql-novice

Hi all,

I created a category table like so:
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) 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 how to apply it in my case.  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 found the below e-mail from a thread on this kind of topic (apparently in 2007, I only copied the relevant text).  The author created a very similar table to mine, but talks more about how to select the child categories, not the root.  I just don't yet have the experience to understand this well yet.  :-)

[begin copied e-mail text]
If the data is static (or if you can get away with running a cron job
every now and then), you can write a recursive pl/pgslq function to
get level information for each node on the tree and assign a specific
"incremental" node_id for each record. Due to the nature of the
recursive function, a node_id is assigned to the children of a
specific node instead of its siblings. You should end up with data as
illustrated below.

id      info    parent_id level node_id
1       Name1   Null    1       1
2       Name2   1       2       2
3       Name3   2       3       3
4      Name4    3       4       4
5      Name5    4       5       5
6      Name5    1       2       6
7      Name6    6       3       7
8      Name7    1       2       8

Then you can simply retrieve the children of node (N) on level (L)
with a single statement.

SELECT * FROM table WHERE node_id > N AND node_id < (SELECT node_id
FROM table WHERE level = L AND node_id > N ORDER BY node_id LIMIT 1
OFFSET 0);

Refrain from using MIN() as performance suffers unbelievably.
[end copied e-mail text]

Thanks in advance!
--
D.C. Parris, FMP, LEED AP O+M, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate