TODO-list
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
=?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
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
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
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
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