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/