Skip site navigation (1) Skip section navigation (2)

Re: Hierarchical queries a la Oracle. Patch.

From: Fernando Nasser <fnasser(at)redhat(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Evgen Potemkin <evgent(at)ns(dot)terminal(dot)ru>,pgsql-patches(at)postgresql(dot)org
Subject: Re: Hierarchical queries a la Oracle. Patch.
Date: 2002-11-26 23:46:29
Message-ID: 3DE407D5.8030101@redhat.com (view raw or flat)
Thread:
Lists: pgsql-patches
Tom Lane wrote:
 >
> Personally I'd prefer to forget Oracle's syntax --- it looks to me like
> it's at least as badly designed as their outer-join syntax, if not worse
> --- and use SQL99's recursive-query syntax for this sort of thing.
> Have you looked at that?
> 

Evgen's query (put in Oracle's syntax):

SELECT * FROM data START WITH id=0 CONNECT BY id = PRIOR pnt;

would have to be implemented by something like:

WITH flat_tree (id, pnt, data, level) AS
   (SELECT id, pnt, data, 1
      FROM data
      WHERE id = 0
    UNION
    SELECT d.in, d.pnt, d.data, f.level + 1
      FROM data d, flat_tree f
      WHERE d.pnt = f.id)
SELECT * FROM flat_tree
ORDER BY level;

(I am simplifying this, one would have to add a path variable to make it depth 
first).

I guess the rewriter could use the START WITH expression to create the first 
select and the CONNECT BY clause to create the second one.  Maybe even the 
parser could do most of the transformation (maybe).

Anyway, the Oracle syntax is indeed more compact, but is not as generic as the 
SQL99 (and IBM DB2) one, so we can always implement it on top of that.

I think even DB2 implements the SQL99 recursion with some restrictions (mostly 
for safety) and that probably covers 99.99% of the uses.  Maybe even a basic 
implementation of the SQL one can accommodate the execution of rewritten Oracle 
CONNECT BY queries.

I agree with Tom that we should implement the SQL99 one first and then, if 
possible, add the Oracle compatibility to it.



-- 
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser(at)redhat(dot)com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9


In response to

pgsql-patches by date

Next:From: Peter EisentrautDate: 2002-11-26 23:51:58
Subject: Re: Resultmap for FreeBSD 4.7
Previous:From: Fernando NasserDate: 2002-11-26 23:27:43
Subject: Re: Hierarchical queries a la Oracle. Patch.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group