pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Roberto Mello <rmello@cc.usu.edu>
To: ?$B;3$5$s <yamahi@isi-webnet.co.jp>
Cc: pgsql-sql@postgresql.org
Subject: Re: Give me a suggestion 'START WITH .. CONNECT BY'.
Date: Wed, 16 May 2001 19:26:15 -0600
Message-ID: <20010516192615.B25655@cc.usu.edu> (raw)
In-Reply-To: <000c01c0dd1a$f1683340$6f40a8c0@isiwebnet.co.jp.isiwebnet.co.jp>
References: <000c01c0dd1a$f1683340$6f40a8c0@isiwebnet.co.jp.isiwebnet.co.jp>

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.



view thread (2+ messages)

Message-ID: <20010516192615.B25655@cc.usu.edu>
Permalink:  ../20010516192615.B25655@cc.usu.edu/
Also on:    postgresql.org/message-id/20010516192615.B25655@cc.usu.edu

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: rmello@cc.usu.edu, yamahi@isi-webnet.co.jp
  Subject: Re: Give me a suggestion 'START WITH .. CONNECT BY'.
  In-Reply-To: <20010516192615.B25655@cc.usu.edu>
  References: <000c01c0dd1a$f1683340$6f40a8c0@isiwebnet.co.jp.isiwebnet.co.jp> <20010516192615.B25655@cc.usu.edu>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox