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
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