Re: Give me a suggestion 'START WITH .. CONNECT BY'.

On Tue, May 15, 2001 at 05:40:32PM +0900, ?$B;3$5$s wrote:
> Hello!
> I try to translate a database to PostgreSQL from ORACLE, but have a regret
> over a function '... START WITH .. CONNECT BY ...'.
> This function is descrived in a sql sentence for ORACLE, and I could not
> find in PostgreSQL.
> For example
> SELECT LEVEL, COMP_ITM, COMP_NAME, COMP_AMT FROM PSFILE
> START WITH PRNT_ITM = 'A0010'
> CONNECT BY PRIOR COMP_ITM = PRNT_ITEM;
>

There's no equivalent for the Oracle tree extensions in PostgreSQL.
Neither LEVEL, START WITH or CONNECT BY PRIOR. There's no easy way out of
this AFAIK.
You'll have to re-write your code in a way that allows you to do this
query in a SQL92 way. In Joe Celko's "SQL For Smarties" he talks about a
nifty algorithm that you can use to do this.
The OpenACS folks used that approach to port Oracle's CONNECT BY to
PostgreSQL, so you could probably ask them over at openacs.org/bboard.

-Roberto

P.S: I plan to add this to my expanded "Porting From Oracle" chapter of
the documentation.
--
+----| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net
http://www.sdl.usu.edu - Space Dynamics Lab, Developer
Air conditioned environment - Do not open Windows.