Re: Hierarchical queries a la Oracle. Patch.

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