Re: Adjacency List & total item counts

use contrib/ltree

     Oleg
On Tue, 9 Aug 2005, Ben wrote:

> Hi
>
> This question is not specific to PostgreSQL but I would like to know
> what is the best way to count the number of items in each node from
> the leaf to the root? Something like this:
>
>                             Computers (100)
>                                    /\
>                                   /  \
>                     CPU (15)      Memory (85)
>
> I have the following SQL schema:
>
> Tree (
>     treeId int,
>     parentId int,
>     name varchar(250),
> )
>
> Item (
>    itemId int,
>    treeId int,
>    expiryDate date
> )
>
> Note that the count for the total number of items in each node depends
> on the item expiry date, i.e. ignore the item if the expiry date is
> older than now().
>
> I have come up with the following solutions but not happy with any one of them:
>
> 1) Do a batch count, i.e. count the number of items every 30 minutes.
> Using this method defeats the purpose of having the count next to each
> node since the number might not be the same as the actual count.
>
> 2) Use trigger but this can be slow since it has to recurse the tree
> and do the sum every time new item is added.
>
> Thanks
> Ben
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83