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:[color=blue]
>
> Couple of ways to do it. One is to use the
> hierarchical query patch
> that mimics Oracle's CONNECT BY
> syntax at
>[/color]
http://www.brasileiro.net/postgres/...one-recipe.adp?[color=blue]
>
> recipe_id=19490.
>
> Another way is to use a nested set model, described
> at
>[/color]
http://www.geocrawler.com/archives/...1/10/0/6961775/,[color=blue]
> which is an extract from the book SQL For Smarties
> by Joe Celko.
>
>
> On Feb 2, 2004, at 4:54 PM, CSN wrote:
>[color=green]
> > 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[/color]
> parent[color=green]
> > 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.[/color]
> Try it![color=green]
> >
http://webhosting.yahoo.com/ps/sb/
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to[/color]
> choose an index scan if[color=green]
> > your
> > joining column's datatypes do not match
> >[/color]
> --------------------
>
> Andrew Rawnsley
> President
> The Ravensfield Digital Resource Group, Ltd.
> (740) 587-0114
>
www.ravensfield.com
>[/color]
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free web site building tool. Try it!
http://webhosting.yahoo.com/ps/sb/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to
Join Bytes! so that your
message can get through to the mailing list cleanly
