Thread: Anything like Oracle's "connect by" SQL?

Hi,

Oracle has a rather handy system for recursing through a table hierarchy.
To give an example, if we had a table representing Companies, with three
fields: company_id, parent_company_id and company_name, you could use the
following SQL to get all parents (and parent of parents, etc) of company_id
1234 like this:

select company_name from Company
connect by company_id = prior parent_company_id
start with company_id = 1234

(My sincere apologies if I'm teaching people to suck eggs - this is my
first post to the list, so for all I know you're all Oracle gurus!)

Is there any kind of function like this in PostgreSQL, or other way to
easily simulate it? I'm currently designing a database which will require a
certain amount of parent-child relationships, and whilst in Oracle I know
the SQL would be simple, I'm not sure how easy it will be without using
"connect by"...

Any help gratefully received!

Cheers,

Tim.

Tim Wright wrote:
> Hi,
>
> Oracle has a rather handy system for recursing through a table hierarchy.

Look for tablefunc (by Joe Conway) in the contribs

HTH
--
  e-Trolley Sayegh & John, Nabil Sayegh
  Tel.: 0700 etrolley /// 0700 38765539
  Fax.: +49 69 8299381-8
  PGP : http://www.e-trolley.de

Tim Wright <postgresql@icehawk.freeserve.co.uk> writes:
> select company_name from Company
> connect by company_id = prior parent_company_id
> start with company_id = 1234

There's not a whole lot of interest around here in emulating Oracle's
proprietary CONNECT BY syntax.  Some work has been done towards
implementing the SQL99 standard's features for "recursive queries"
(WITH et al), but it's unfinished work as of 7.4.

My general impression is that the spec's syntax for recursion is
considerably more flexible and powerful than CONNECT BY, but
correspondingly less easy to use :-(.  Sorta the outer-join story
all over again.

We might have WITH for 7.5, but I'd not want to swear to it.

            regards, tom lane

Tom,

> There's not a whole lot of interest around here in emulating Oracle's
> proprietary CONNECT BY syntax.  Some work has been done towards
> implementing the SQL99 standard's features for "recursive queries"
> (WITH et al), but it's unfinished work as of 7.4.

But we have CONNECT_BY as a function, thanks to Joe Conway.

--
Josh Berkus
Aglio Database Solutions
San Francisco