Re: Hierarchal data

Hi Bill
You can do exactly what you want using plpgsql (which is what I assume
you mean by 'using posgtgres' and recursion. I use the adjacent key id
to track all my projects and tasks.

Here is my example of drilling from node to root:
-------------

CREATE OR REPLACE FUNCTION public.fx_root_job(int4)
RETURNS int4 AS
'

declare
x int4;
returnvalue int4;
begin
raise notice \'looking for root of %\',$1;
select into x id_parent_ from job
where id_ = $1;

/* The top of the tree is when the
parent field is 0 or a job is its own parent*/

if x = $1 or x = 0 or x isnull then
returnvalue = $1;
else
returnvalue = fx_root_job( x );
end if;
return returnvalue;
end;'
LANGUAGE 'plpgsql' VOLATILE;

--------

I was going to use Joe Celko's id, but I'd already started - but _next_
time. I was so inspired by the article, I bought the book it came from.
So pissed off I didn't think of it my self.

Hope this is similar enough to what your after to be helpful
Glenn

On Fri, 2004-01-23 at 12:28, Bill Moseley wrote:
> I realize this is a classic problem, but I'm a NOVICE after all.
>
> I want to represent hierarchal topics (just like dmoz.org) I've seen
> two ways to represent the data. Both are described at
>
> http://www.sitepoint.com/article/1105/1
>
> And in another article by Joe Celko about using Modified Preorder
Trees.
>
> I'm leaning toward using the simpler "adjacency list model" where each
> node (topic) in the tree just lists its parent.
>
> create table topic (
> topic_id serial PRIMARY KEY,
> name varchar(64),
> parent_id int -- possible to use "REFERENCES topic" but
allow NULL?
> )
>
>
> The problem becomes then how to find the path from a given node to the
> root node. I'm working with perl and currently what I'm doing is a
> recursive call to the database. That's going to be slow if I have to
> look up many of those.
>
> My question is this: is there a way to get Postgresql to do this
recursive
> query for me?
>
>
> My other question is how to get from a topics path to a topic node
id. That is,
> can someone suggest a way to find the topic id if you have a path
like:
>
> /top/Computers/Software/Operating_Systems/Open_Source/
>
>
>
>
>
>
> Thanks,