Re: recursive query?

I figured out a function that does it. Anything to
worry about performance-wise? Right now, there's not
more than 5 levels between any child and top-most
parent.

----------


create or replace function get_parent_nodes2 (
int
) returns setof nodes
as '

declare

    pParentID alias for $1;

    rNode nodes;

begin

    select into rNode * from nodes where id = pParentID;

    if found then
        return next rNode;

        while not rNode.parent_id is null loop
            select into rNode * from nodes where id =
rNode.parent_id;

            return next rNode;
        end loop;
    end if;

    return;

end;

' language plpgsql;

----



--- Andrew Rawnsley <ronz@ravensfield.com> wrote:
>
> Couple of ways to do it. One is to use the
> hierarchical query patch
> that mimics Oracle's CONNECT BY
> syntax at
>
http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?
>
> recipe_id=19490.
>
> Another way is to use a nested set model, described
> at
>
http://www.geocrawler.com/archives/3/6/2001/10/0/6961775/,
> which is an extract from the book SQL For Smarties
> by Joe Celko.
>
>
> On Feb 2, 2004, at 4:54 PM, CSN wrote:
>
> > I have a table with these columns:
> >
> > id, node, parent_node_id
> >
> > The top-most nodes would have a parent_node_id of
> > NULL. Is it possible to get a node, and all its
> parent
> > nodes, in a single query?
> >
> > For example, a node might be:
> >
> > books > computers > databases > oss > postgres
> >
> > and the rows fetched would be:
> >
> > 1,books,NULL
> > 2,computers,1
> > 3,databases,2
> > 4,oss,3
> > 5,postgres,4
> >
> > TIA,
> > CSN
> >
> >
> >
> > __________________________________
> > Do you Yahoo!?
> > Yahoo! SiteBuilder - Free web site building tool.
> Try it!
> > http://webhosting.yahoo.com/ps/sb/
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to
> choose an index scan if
> > your
> >       joining column's datatypes do not match
> >
> --------------------
>
> Andrew Rawnsley
> President
> The Ravensfield Digital Resource Group, Ltd.
> (740) 587-0114
> www.ravensfield.com
>


__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/