On 15/07/10 17:55, Alban Hertroys wrote:
> On 15 Jul 2010, at 3:05, Craig Ringer wrote:
>
>> It was an example of how it'd be nice to avoid the need for a join when
>> dealing with scalar values. I'd love to be able to write:
>>
>> WITH aconstant AS (1)
>> SELECT x.*, aconstant FROM generate_series(1,10) AS x;
>>
>> ... but can't presently do so because the WITH terms are only visible as
>> potential from-list items.
>
>
> But why is that a problem? The below seems to work just fine:
>
> dalroi=> WITH constants AS (SELECT 1 AS aconstant)
> SELECT 'x' AS column, aconstant FROM constants WHERE aconstant = 1;
> column | aconstant
> --------+-----------
> x | 1
> (1 row)
>
> Sure, you'll have to join with the with-query to get any meaningful results, but if there are any complicated
calculationsin that query, won't they be calculated just once - like you intended?
>
> If not, I think we're all failing to see the point you're trying to make.
The join its self is expensive. See the plans I posted in the post
you're replying to.
--
Craig Ringer