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

Re: Hierarchal data

From: glenn <vmstech(at)tpg(dot)com(dot)au>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Hierarchal data
Date: 2004-01-26 20:25:56
Message-ID: 1075148756.14913.430.camel@odin.valhalla (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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)

        x int4;
        returnvalue int4;
        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;
                returnvalue = fx_root_job( x );
        end if;
        return returnvalue;


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

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  I've seen
> two ways to represent the data.  Both are described at
> And in another article by Joe Celko about using Modified Preorder
> 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
> 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
>    /top/Computers/Software/Operating_Systems/Open_Source/
> Thanks,

In response to

pgsql-novice by date

Next:From: Marcel WolfDate: 2004-01-26 20:37:04
Subject: Database creation
Previous:From: April CarvalhoDate: 2004-01-26 20:18:34
Subject: Re: Problem with pg_hba.conf file in Postgres 7.3

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