Start With... Connect By?

Philippe Lang, 13.07.2009 08:05:

Hi,

Now that Postgresql 8.4 has a "WITH RECURSIVE" operator (thanks! :)), is
there a chance we can see one day "START WITH... CONNECT BY" in
Postgresql, or is that something 100% oracle-specific?

START WITH is Oracle specific whereas recursive CTEs are an ANSI Standard (supported by PostgreSQL, Firebird and SQL Server).

As far as I can tell anything you can do with CONNECT BY can be done with WITH RECURSIVE (including ORDER SIBLINGS BY), it's just a bit more "noise" because you need to write more stuff (I wish the ANSI standard had adopted the CONNECT BY, it's really very elegant)

Thomas

2009/7/13 Philippe Lang <philippe.lang@attiksystem.ch>:

Hi,

Now that Postgresql 8.4 has a "WITH RECURSIVE" operator (thanks! :)), is
there a chance we can see one day "START WITH... CONNECT BY" in
Postgresql, or is that something 100% oracle-specific?

It is not probable.

regards
Pavel Stěhule

On Mon, Jul 13, 2009 at 3:35 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote:

2009/7/13 Philippe Lang <philippe.lang@attiksystem.ch>:

Hi,

Now that Postgresql 8.4 has a "WITH RECURSIVE" operator (thanks! :)), is
there a chance we can see one day "START WITH... CONNECT BY" in
Postgresql, or is that something 100% oracle-specific?

It is not probable.

Isn't there a connectby in the tablefuncs contrib module?

2009/7/13 Scott Marlowe <scott.marlowe@gmail.com>:

On Mon, Jul 13, 2009 at 3:35 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote:

2009/7/13 Philippe Lang <philippe.lang@attiksystem.ch>:

Hi,

Now that Postgresql 8.4 has a "WITH RECURSIVE" operator (thanks! :)), is
there a chance we can see one day "START WITH... CONNECT BY" in
Postgresql, or is that something 100% oracle-specific?

It is not probable.

Isn't there a connectby in the tablefuncs contrib module?

it has similar functionality, but syntax is far to Oracle

Pavel

On Mon, Jul 13, 2009 at 08:23:56AM +0200, Thomas Kellerer wrote:

Philippe Lang, 13.07.2009 08:05:

Hi,

Now that Postgresql 8.4 has a "WITH RECURSIVE" operator (thanks!
:)), is there a chance we can see one day "START WITH... CONNECT
BY" in Postgresql, or is that something 100% oracle-specific?

START WITH is Oracle specific whereas recursive CTEs are an ANSI
Standard (supported by PostgreSQL, Firebird and SQL Server).

Not to mention DB2. I'm not sure how close Firebird is to actually
shipping them...

As far as I can tell anything you can do with CONNECT BY can be done
with WITH RECURSIVE (including ORDER SIBLINGS BY), it's just a bit
more "noise" because you need to write more stuff (I wish the ANSI
standard had adopted the CONNECT BY, it's really very elegant)

I don't. Apart from any IP issues that may obtain, CONNECT BY gets
its "elegance" for simple queries at the cost of nightmarish
kludginess for queries not quite as simple. I have a book by a
current Oracle employee--Vadim Tropashko's SQL Design Patterns--that
bemoans the lack of availability of CTEs in Oracle.

Once you get used to CTEs, you'll wonder how you ever programmed in
SQL without them :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter, 13.07.2009 16:32:

START WITH is Oracle specific whereas recursive CTEs are an ANSI
Standard (supported by PostgreSQL, Firebird and SQL Server).

Not to mention DB2. I'm not sure how close Firebird is to actually
shipping them...

Interesting, didn't know DB2 had them as well.

Firebird implemented recursive CTEs with Version 2.1 which was released April, 2008, more than a year ago. Which makes it the first OpenSource DBMS to implement them I guess.

Thomas

On Mon, Jul 13, 2009 at 2:05 AM, Philippe Lang <philippe.lang@attiksystem.ch

wrote:

Hi,

Now that Postgresql 8.4 has a "WITH RECURSIVE" operator (thanks! :)), is
there a chance we can see one day "START WITH... CONNECT BY" in
Postgresql, or is that something 100% oracle-specific?

There is a commercial / proprietary version of the pg database called
'EnterpriseDB Advanced Server' that supports this.
www.enterprisedb.com

--Scott

On Mon, Jul 13, 2009 at 2:05 AM, Philippe Lang <philippe.lang@attiksystem.ch

wrote:

Hi,

Now that Postgresql 8.4 has a "WITH RECURSIVE" operator (thanks! :)), is
there a chance we can see one day "START WITH... CONNECT BY" in
Postgresql, or is that something 100% oracle-specific?

There is a commercial / proprietary version of the pg database called
'EnterpriseDB Advanced Server' that supports this.
www.enterprisedb.com

--Scott