As someone else mentioned Joe Celko's book has some good stuff in it. You
might also try this code snippet. It's currently in PHP and happens
outside of the database, but you should get a feeling for it. It works
pretty well for me.
http://stuff.adhesivemedia.com/php/heirarchial-sorting.php
-philip
On Thu, 13 Dec 2001, Fran Fabrizio wrote:
>
> Hello,
>
> I've got a plpgsql function that is recursive. Basically, it traverses
> a table that represents a tree, which in turn represents parent-child
> relationships. So, I have a function, get_descendants. For each pass,
> it gets the children of some id. Then it recurses and looks for the
> children of all of those children, etc...So, along the way, I'm building
> a list of ids that represent the whole family.
>
> For lack of a better idea, I'm storing the id's into a table on each
> pass. So, if I recurse three levels, I'm doing three inserts. When the
> recursion exits, I simply select the entire table and then I delete all
> rows from it. The performance hit I take is unacceptable, something
> like .02 - .03 seconds per insert, and it's adding up due to the amount
> of times I have to run this function. The end result is that the web
> page that displays this data takes many seconds to run.
>
> Is there some sort of data structure in plpgsql (an array) that I can
> use instead of the hack of inserting into a table on each pass and
> selecting back out at the end? I have to find a way to optimize this
> process further.
>
> Thanks,
> Fran
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>