Recently I found an Oracle extension, START WITH .. CONNECT BY, which
greatly eases parsing of n-way tree structures stored in table format.

I'd prefer implementing this myself, but alas, I feel I need something
which is a bit easier, to get accustomed to the code base.

the following is copyrighted by Oracle Corporation. So, sue me for posting
it. :)

START WITH condition 

Specify a condition that identifies the row(s) to be used as the
root(s) of a hierarchical query. Oracle uses as root(s) all rows that
satisfy this condition. If you omit this clause, Oracle uses all rows in
the table as root rows. The START WITH condition can contain a subquery.  

CONNECT BY condition 

Specify a condition that identifies the relationship between parent rows
and child rows of the hierarchy. condition can be any condition as
described in "Conditions". However, some part of the condition must use
the PRIOR operator to refer to the parent row. The part of the condition
containing the PRIOR operator must have one of the following forms: 

PRIOR expr comparison_operator expr 

expr comparison_operator PRIOR expr 

Restriction: The CONNECT BY condition cannot contain a subquery. 


Ola

-- 
Ola Sundell
ola@miranda.org - olas@wiw.org - ola.sundell@personalchemistry.com
http://miranda.org/~ola


Tue, 12 Feb 2002 09:53:49 -0500 (EST)
Ola Sundell <ola@miranda.org>
feature request START WITH ... CONNECT BY
> Recently I found an Oracle extension, START WITH .. CONNECT BY, which
> greatly eases parsing of n-way tree structures stored in table format.
> 
> I'd prefer implementing this myself, but alas, I feel I need something
> which is a bit easier, to get accustomed to the code base.
> 

Hi, 

I am currently porting business application from Oracle to PG, for one of our
customers.

I work hardly on migrating CONNECT BY queries, using the really good libraries
from OpenACS project (version 4).

It a kind of 1 day/man to enable tree structure in PG for one table accessed
with a CONNECT BY query.

So I'll be really glad to you if you enable CONNECT BY statments in PG!!

So, what you want to know about connect by statment?

read:
http://www.arsdigita.com/books/sql/trees.html

and:
http://openacs.org/bboard/q-and-a-fetch-msg.tcl?msg_id=0000j6&topic_id=12&topic=OpenACS%204%2e0%20Design

download OpenACS source code for version 4.x and :

We're using this extensively in openacs4. If you download the latest code, you
can find the tree encoding table and some tree-utility routines in
       openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql. For
corresponding trigger routines look at
       openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql -
specifically look at the acs_objects table definition and its associated
       triggers. 

       With regards to inheritance, we looked seriously at prior to starting the
opeancs4 porting activities and we opted not to use it because it was
       deficient in serveral areas. 

       -- Dan Wickstrom, September 7, 2001 


Hope this helps

-- 
Jean-Paul ARGUDO                     		IDEALX S.A.S
Consultant bases de donnï¿½es			15-17, av. de Sï¿½gur
http://IDEALX.com/ 				F-75007 PARIS

Tue, 12 Feb 2002 18:21:14 +0100
Jean-Paul ARGUDO <jean-paul.argudo@idealx.com>
Re: feature request START WITH ... CONNECT BY
On Tue, 2002-02-12 at 19:53, Ola Sundell wrote:
> Recently I found an Oracle extension, START WITH .. CONNECT BY, which
> greatly eases parsing of n-way tree structures stored in table format.
> 

It's in TODO as WITH RECURSIVE, which is the SQL3 way of doing it, but I
don't know if anyone is seriously working on it. 

I have done a little investigation, and I think that this could be
doable without too much changes in planner/executor by doing repeated
merge or hash joins. 

If we want automatic checks for infinite recursion there are also two
ways of doing it:
  1) use a has of already selected rows or
  2) pick new rows from a realize'd table and mark them as removed
     there.

-------------
Hannu


13 Feb 2002 09:21:52 +0500
Hannu Krosing <hannu@krosing.net>
Re: feature request START WITH ... CONNECT BY
I jump again on this mail to ask for SQL help. Since I've not found a list on
pgsql-users or something like that, I apologize to post it to hackers..

So.. I'm porting from Oracle to PG. I have many CONNECT BY queries, luckyly,
only 2 tables are hierarchical. I've adopted OpenACS solution, since I am sure
it is the best way to do with that problem.

But, I found a problem wich I have no brain left today to resolve.
I port following Oracle CONNECT BY statment:

--ORACLE QUERY
--
--select
--        sum(t01_caf) SCAF,
--        sum(t01_itm_cnt) SART
--from T01_&DateData
--start with T01_upr_lvl_typ = &TypNiv and T01_upr_lvl_nbr = &Niv
--connect by prior T01_lvl_typ = T01_upr_lvl_typ and prior T01_lvl_nbr =
T01_upr_lvl_nbr
--
-- The execution in the Oracle DB returns: 
--
--
--      SCAF       SART
------------ ----------
--40164802,4    1404296
--
-- with variables &TypNiv = 0 et &Niv = 0
--
-- PG port:
--
\set TypNiv 0
\set Niv 0
--
select
        sum(t01_caf) as SCAF,
        sum(t01_itm_cnt) as SCAF
from
        t01_20011231
where
        strpos(t01_tree_sortkey,(select t01_tree_sortkey
                                   from t01_20011231
                                  where t01_lvl_typ = :TypNiv
                                    and t01_lvl_nbr = :Niv))=1
group by
        ???;

The problem is that I am no longuer able to find the RIGHT group by statment :-/

Can someone help me ? I'm sure it is surely kind simplistic? dunno..

Ah! The purpose of the query is to sum values on all nodes children of one node.
Inthis crappy customer database, a node is identifyied uniquely with couple
(t01_lvl_typ,t01_lvl_nbr), ((couple t01_upr_lvl_typ,t01_upr_lvl_nbr identifies
uniquely the Father of the node)) because there can be nodes at different level
(lvl_typ) with the same identifyier (lvl_nbr). I dont want to user concat || to
create a pseudo-unique-identifyer, because I think there may be perfs problems
...

Thanks.  Best regards & wishes.


-- 
Jean-Paul ARGUDO                     		IDEALX S.A.S
Consultant bases de donnï¿½es			15-17, av. de Sï¿½gur
http://IDEALX.com/ 				F-75007 PARIS

Wed, 13 Feb 2002 13:04:29 +0100
Jean-Paul ARGUDO <jean-paul.argudo@idealx.com>
Re: feature request START WITH ... CONNECT BY
At last, here's the solution: NO NEED to group by :-/ ???

Now I ask for a WHY to hackers :)

select
        sum(t01_caf) as SCAF,
        sum(t01_itm_cnt) as SCAF
from
        t01_20011231
where
        strpos(t01_tree_sortkey,(select t01_tree_sortkey
                                   from t01_20011231
                                  where t01_upr_lvl_typ = :TypNiv
                                    and t01_upr_lvl_nbr = :Niv))=1;

--
-- Stangely, I don't really understand why, there is no need of group by clause 
-- there!
--
-- Here's the result:
--    scaf     |  scaf   
---------------+---------
-- 40164802.36 | 1404296
--(1 row)

Thanks

-- 
Jean-Paul ARGUDO                     		IDEALX S.A.S
Consultant bases de donnï¿½es			15-17, av. de Sï¿½gur
http://IDEALX.com/ 				F-75007 PARIS

Wed, 13 Feb 2002 16:49:23 +0100
Jean-Paul ARGUDO <jean-paul.argudo@idealx.com>
Re: feature request START WITH ... CONNECT BY