Thread: Start With... Connect By?

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?

Best regards,

Philippe Lang

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'sjust a bit more "noise" because you need to write more stuff (I wish the ANSI standard had adopted the CONNECT BY,
it'sreally 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


>
> Best regards,
>
> Philippe Lang
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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
itthe first OpenSource DBMS to implement them I guess. 

Thomas