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