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

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

From
山さん
Date:
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;
 
I  want to know an similar SQL description in PostgreSQL. Will you give me a suggestion?
                                                              Thanks! 
Hirosi Yamaoka
Co.ltd Integrated Systems Institute
701-0211 688-55 Higasiune, Okayama-city, Okayama-pref. Japan

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

From
Roberto Mello
Date:
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.