Postgres Pro
Downloads
Home   >   mailing lists

Thread: Proposal of hierachical queries (a la Oracle)

Proposal of hierachical queries (a la Oracle)

From
Evgen Potemkin
Date:
Hi there!

I want to propose the patch for adding the hierarchical queries posibility.
It allows to construct queries a la Oracle for ex:
SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond;B

I've seen this type of queries often made by adding a new type, which stores
position of row in the tree. But sorting such tree are very tricky (i think).

Patch allows result tree to be sorted, i.e. subnodes of each node will be
sorted by ORDER BY clause.

with regards, evgen

---
.evgen

Re: Proposal of hierachical queries (a la Oracle)

From
Rod Taylor
Date:
On Thu, 2002-11-14 at 06:52, Evgen Potemkin wrote:
> Hi there!
> 
> I want to propose the patch for adding the hierarchical queries posibility.
> It allows to construct queries a la Oracle for ex:
> SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond;B

Great addition.  But please use the SQL 99 syntax for recursive queries
(if you need the full segment, I can send them to you):

Section 7.13 of Part 2:
        Format
        <search or cycle clause> ::=               <search clause>             | <cycle clause>             | <search
clause><cycle clause>
 
        <search clause> ::=             SEARCH <recursive search order> SET <sequence column>
        <recursive search order> ::=               DEPTH FIRST BY <sort specification list>             | BREADTH FIRST
BY<sort specification list>
 
        <sequence column> ::= <column name>
        <cycle clause> ::=             CYCLE <cycle column list>             SET <cycle mark column> TO <cycle mark
value>            DEFAULT <non-cycle mark value>             USING <path column>
 
        <cycle column list> ::=             <cycle column> [ { <comma> <cycle column> }... ]
        <cycle column> ::= <column name>
        <cycle mark column> ::= <column name>
        <path column> ::= <column name>
        <cycle mark value> ::= <value expression>
        <non-cycle mark value> ::= <value expression>

-- 
Rod Taylor <rbt@rbt.ca>

Re: Proposal of hierachical queries (a la Oracle)

From
Evgen Potemkin
Date:
Ok, full section would be very helpful, i don't have it.

---
.evgen

On 15 Nov 2002, Rod Taylor wrote:

> On Thu, 2002-11-14 at 06:52, Evgen Potemkin wrote:
> > Hi there!
> >
> > I want to propose the patch for adding the hierarchical queries posibility.
> > It allows to construct queries a la Oracle for ex:
> > SELECT a,b FROM t CONNECT BY a PRIOR b START WITH cond;B
>
> Great addition.  But please use the SQL 99 syntax for recursive queries
> (if you need the full segment, I can send them to you):
>
> Section 7.13 of Part 2:
>