469,140 Members | 1,137 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

functionality like Oracle's "connect by"

I know this question has been discussed, probably multiple times, but I
can't seem to access archives.postgresql.org today....

I need to select all the rows in a table with two fields: parent_id and
child_id that participate in the same logical "tree". In other words,
given an id value, I want to find where child_id = my-value, and then
retrieve the values in the tree above it.

The functionality I'm looking for is essentially the Oracle "connect
by". Does anything like this exist for postgresql? If not, does anybody
have a clever solution? (My not-so-clever first pass involved recursing
in my Java program, but the stack-space-abuse police are knocking on my
door....)

Again, apologies for asking something that's probably already been
discussed ad nauseum on this list...

- DAP
================================================== ====
David Parker Tazz Networks (401) 709-5130


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
2 1626

"David Parker" <dp*****@tazznetworks.com> writes:
The functionality I'm looking for is essentially the Oracle "connect
by". Does anything like this exist for postgresql?


Yes. In the contrib directory of the source is a directory named "tablefunc".
If you've installed from an distribution you might like for a
postgresql-contrib package or something like that.

It has a function that tries to do what you want:
connectby(text relname, text keyid_fld, text parent_keyid_fld
[, text orderby_fld], text start_with, int max_depth
[, text branch_delim])
- returns keyid, parent_keyid, level, and an optional branch string
and an optional serial column for ordering siblings
- requires anonymous composite type syntax in the FROM clause. See
the instructions in the documentation below.
I've never tried it though.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #2
David,

I suggest our search on postgresql resources:
http://www.pgsql.ru/db/pgsearch/inde...t+by%22+oracle
Oleg

On Mon, 30 Aug 2004, David Parker wrote:
I know this question has been discussed, probably multiple times, but I
can't seem to access archives.postgresql.org today....

I need to select all the rows in a table with two fields: parent_id and
child_id that participate in the same logical "tree". In other words,
given an id value, I want to find where child_id = my-value, and then
retrieve the values in the tree above it.

The functionality I'm looking for is essentially the Oracle "connect
by". Does anything like this exist for postgresql? If not, does anybody
have a clever solution? (My not-so-clever first pass involved recursing
in my Java program, but the stack-space-abuse police are knocking on my
door....)

Again, apologies for asking something that's probably already been
discussed ad nauseum on this list...

- DAP
================================================== ====
David Parker Tazz Networks (401) 709-5130
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


Regards,
Oleg
__________________________________________________ ___________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: ol**@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Hunter | last post: by
3 posts views Thread by cberthu | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.