Thread: 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
Dennis Björklund <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 rphWHERE 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) NATURALLEFT 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: > Dennis Björklund <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: > Dennis Björklund <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