Re: Start With... Connect By?

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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