Skip site navigation (1) Skip section navigation (2)

Stuck Up In My Category Tree

From: Don Parris <parrisdc(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Stuck Up In My Category Tree
Date: 2011-08-05 18:40:21
Message-ID: (view raw or flat)
Lists: 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 =

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

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  |
GPG Key ID: F5E179BE


pgsql-novice by date

Next:From: JORGE MALDONADODate: 2011-08-08 17:06:43
Subject: Reorganizing a database objects
Previous:From: damien clochardDate: 2011-08-04 21:43:00
Subject: Re: GUI tools to edit schema

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group