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

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

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

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