Items that have names after them in the TODO-list, do these names indicate
that the persons are working on it now?

The item i'm extra curious about is

 * Add SQL99 WITH clause to SELECT (Tom, Fernando)

There is also an item for handling recursive such clauses.

The simple non recursive case can be implemented in a straight forward
(but stupid) way, where one "just" substitute in for the variables. It
won't give any sharing effects that one can get by declaring a variable
once and use it several times. It will however make it much easier to
write readable SQL expressions. The sharing can be seen as just an 
optimization anyway.

Also, it's not at all clear to me how to decide when one want to calculate
the subexpression first and use in the other places and when to duplicate
the expression to let the optimizer work with it. I however just want it
in order to make my SQL-code simpler and more expressive.

Maybe I should also point out that I have not implemented this so I can't
be sure that it really is just as simple as just substituting. It looks
like it after a quick look, that's all!

--
/Dennis


Mon, 21 Apr 2003 07:17:29 +0200 (CEST)
=?ISO-8859-1?Q?Dennis_Bj=F6rklund?= <db@zigo.dhs.org>
TODO-list
=?ISO-8859-1?Q?Dennis_Bj=F6rklund?= <db@zigo.dhs.org> writes:
> The item i'm extra curious about is
>  * Add SQL99 WITH clause to SELECT (Tom, Fernando)

Actually, Andrew Overholt is the guy doing the work on that ...
Fernando and I are, um, supervising.

> The simple non recursive case can be implemented in a straight forward
> (but stupid) way, where one "just" substitute in for the variables.

I don't see a lot of interest in the non-recursive case; it seems just
a bizarre alternate syntax for subselect-in-FROM.  The recursive case
is interesting and useful though.

			regards, tom lane


Mon, 21 Apr 2003 02:25:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: TODO-list 
On Mon, 21 Apr 2003, Tom Lane wrote:

> I don't see a lot of interest in the non-recursive case; it seems just
> a bizarre alternate syntax for subselect-in-FROM.  The recursive case
> is interesting and useful though.

The nonrecursive case does not give you anything that you can't get 
without. It's just about giving names to subexpression.

I think people just don't know about the nice syntax that they could have
had. For those who don't know about the WITH-syntax, let me show you an
example from one of my apps. First the old syntax:

  SELECT p.pid, name, score
    FROM (((SELECT pid FROM result WHERE mid = 112)
           UNION
           (SELECT pid FROM rph WHERE mid = 112)
           )
          NATURAL LEFT OUTER JOIN
          (SELECT pid, score FROM calculated_result 
           WHERE mid = 112)
         ) AS x, person p
   WHERE x.pid = p.pid
ORDER BY name

and with SQL99 syntax:

  WITH
    result_pids AS SELECT pid
                   FROM result
                   WHERE mid = 112
    rph_pids    AS SELECT pid
                   FROM rph
                   WHERE mid = 112
    scores      AS SELECT pid, score
                   FROM calculated_result
                   WHERE mid = 112
  SELECT p.pid, name, score
    FROM (result_pids UNION rph_pids)
         NATURAL LEFT OUTER JOIN
         scores AS x, person p
   WHERE x.pid = p.pid
ORDER BY name;

The only "real" gain comes if you use a subexpression several times (not
in the example above) where you have to cut'n'paste the expression if you 
don't have WITH.

-- 
/Dennis


Mon, 21 Apr 2003 12:46:22 +0200 (CEST)
=?ISO-8859-1?Q?Dennis_Bj=F6rklund?= <db@zigo.dhs.org>
Re: TODO-list 
Tom Lane kirjutas E, 21.04.2003 kell 09:25:
> =?ISO-8859-1?Q?Dennis_Bj=F6rklund?= <db@zigo.dhs.org> writes:
> > The item i'm extra curious about is
> >  * Add SQL99 WITH clause to SELECT (Tom, Fernando)
> 
> Actually, Andrew Overholt is the guy doing the work on that ...

Any pointers to the work he has done ?

I have done some as well, with the final aim being WITH RECURSIVE

> Fernando and I are, um, supervising.
> > The simple non recursive case can be implemented in a straight forward
> > (but stupid) way, where one "just" substitute in for the variables.
> 
> I don't see a lot of interest in the non-recursive case; it seems just
> a bizarre alternate syntax for subselect-in-FROM. 

There are useful cases when the tree of subqueries forks, i.e. where 2nd
and 3rd subselects in WITH both use the first.

> The recursive case is interesting and useful though.

Is Andrew Overholt doing the simple case (alternative to the the table
function we already have) or is he trying to solve any/all of the
general cases ?

Is he doing also the <search or cycle clause> and depth/breadth first
variants ?

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


21 Apr 2003 17:05:14 +0300
Hannu Krosing <hannu@tm.ee>
Re: TODO-list
Tom Lane kirjutas E, 21.04.2003 kell 09:25:
> =?ISO-8859-1?Q?Dennis_Bj=F6rklund?= <db@zigo.dhs.org> writes:
> > The item i'm extra curious about is
> >  * Add SQL99 WITH clause to SELECT (Tom, Fernando)
> 
> Actually, Andrew Overholt is the guy doing the work on that ...

Any pointers to the work he has done ?

I have done some as well, with the final aim being WITH RECURSIVE

> Fernando and I are, um, supervising.
> > The simple non recursive case can be implemented in a straight forward
> > (but stupid) way, where one "just" substitute in for the variables.
> 
> I don't see a lot of interest in the non-recursive case; it seems just
> a bizarre alternate syntax for subselect-in-FROM. 

There are useful cases when the tree of subqueries forks, i.e. where 2nd
and 3rd subselects in WITH both use the first.

> The recursive case is interesting and useful though.

Is Andrew Overholt doing the simple case (alternative to the the table
function we already have) or is he trying to solve any/all of the
general cases ?

Is he doing also the <search or cycle clause> and depth/breadth first
variants ?

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


21 Apr 2003 17:06:10 +0300
Hannu Krosing <hannu@tm.ee>
Re: TODO-list
> Any pointers to the work he has done ?

I haven't committed anything yet.  I was distracted with some other stuff
for the past two weeks or so and haven't made much headway.  I'd be happy
to work with you if you want.

> Is Andrew Overholt doing the simple case (alternative to the the table
> function we already have) or is he trying to solve any/all of the
> general cases ?

I was aiming for the general case.

> Is he doing also the <search or cycle clause> and depth/breadth first
> variants ?

I was going to add this in after I had basic functionality.  It was
something that I was definitely going to put in there, but it wasn't my
first priority.

Andrew


Tue, 22 Apr 2003 10:31:23 -0400
Andrew Overholt <overholt@redhat.com>
Re: TODO-list