Thread: Hirarchical queries a la Oracle. Patch.

Hi there!

Patch is posted to pgsql-patches. docs inside.
SQL 99 version will be later.

regards,

---
.evgen

Evgen Potemkin kirjutas R, 22.11.2002 kell 15:57:
> Hi there!
>
> Patch is posted to pgsql-patches. docs inside.

It would of course be nice to support both Oracle and ISO/ANSI syntaxes,
but I'm afraid that the (+) may clash with our overloadable operators
feature.

> SQL 99 version will be later.

I attach a railroad diagram of SQL99 "WITH RECURSIVE" and a diff against
mid-summer gram.y which implements half of SQL99 _syntax_ (just the WITH
{RECURSIVE} part, SEARCH (tree search order order) and CYCLE (recursion
control) clauses are missing).

WITH clause seems to be quite useful in its own right as well, not just
for recursive queries, so I guess that someone with good knwledge of
postgresql internals could get plain WITH working quite fast -

The main difference between subqueries defined in WITH clause and in
FROM clause is that while subqueries in FROM don't see each other in
their namespaces, the ones in WITH either see all preceeding ones (plain
with) or just all in WITH clause (WITH RECURSIVE)

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

thanks, it's VERY helpful.
understanding SQL99 draft is a bit more difficult than i thought :)

regards,

---
.evgen

On 27 Nov 2002, Hannu Krosing wrote:

> I attach a railroad diagram of SQL99 "WITH RECURSIVE" and a diff against
> mid-summer gram.y which implements half of SQL99 _syntax_ (just the WITH
> {RECURSIVE} part, SEARCH (tree search order order) and CYCLE (recursion
> control) clauses are missing).
>

On Thu, 2002-11-28 at 17:34, Evgen Potemkin wrote:
> thanks, it's VERY helpful.
> understanding SQL99 draft is a bit more difficult than i thought :)

You might also try to get DB2 installed somewhere (IIRC IBM gives out 
limited time developer copies).

It implements at least the basic recursive query (without requiring the word RECURSIVE :)

> 
> --------------
> Hannu

Hannu Krosing <hannu@tm.ee> writes:
> On Thu, 2002-11-28 at 17:34, Evgen Potemkin wrote:
>> understanding SQL99 draft is a bit more difficult than i thought :)

> You might also try to get DB2 installed somewhere (IIRC IBM gives out 
> limited time developer copies).

Even without DB2 installed, you can read the documentation for it on the
web.  There's quite a lot of useful info in IBM's docs.  For example
http://nscpcw.physics.upenn.edu/db2_docs/db2s0/withsel.htm
and the example starting at
http://nscpcw.physics.upenn.edu/db2_docs/db2s0/db2s0446.htm

(The docs are probably also readable directly from IBM, but this is the
first copy I found by googling...)
        regards, tom lane