469,090 Members | 1,143 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

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

Nov 22 '05 #1
2 5712

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.
(740) 587-0114
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

Nov 22 '05 #2
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.
(740) 587-0114
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

Nov 22 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by replace-this-with-my-name | last post: by
2 posts views Thread by Perttu Pulkkinen | last post: by
3 posts views Thread by Dennis Haney | last post: by
3 posts views Thread by NatRoger | last post: by
2 posts views Thread by Jim Devenish | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.