recursive query?

[CSN]

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]

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/c...ne-recipe.adp?
recipe_id=19490.

Another way is to use a nested set model, described at
http://www.geocrawler.com/archives/3.../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.
[phone]
www.ravensfield.com
---------------------------(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

[CSN]

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 <ro**@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/c...ne-recipe.adp?
recipe_id=19490.

Another way is to use a nested set model, described
at
http://www.geocrawler.com/archives/3.../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.
[phone]
www.ravensfield.com

__________________________________
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly