On 15/07/10 00:34, David Fetter wrote: >> => WITH aconstant(constval) AS (VALUES(1)) SELECT x.*, constval FROM >> generate_series(1,10) AS x; >> ERROR: column "constval" does not exist >> LINE 1: ...TH aconstant(constval) AS (VALUES(1)) SELECT x.*, constval F... > > You missed the CROSS JOIN, which you could make implicit, even though > implicit CROSS JOINs are bad coding style: 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. > WITH aconstant(constval) AS (VALUES(1)) > SELECT x.*, constval > FROM > generate_series(1,10) AS x > CROSS JOIN > aconstant; > x | constval > ----+---------- > 1 | 1 > 2 | 1 > 3 | 1 > 4 | 1 > 5 | 1 > 6 | 1 > 7 | 1 > 8 | 1 > 9 | 1 > 10 | 1 > (10 rows) Using a cross join can often result in an undersired and expensive nested loop, (I think) materialize, etc. In this case, the planner is using a nested loop to join `aconstant' with the output of the function scan: > Nested Loop (cost=0.01..22.53 rows=1000 width=8) (actual time=0.049..0.133 rows=10 loops=1) > CTE aconstant > -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) (actual time=0.004..0.006 rows=1 loops=1) > -> CTE Scan on aconstant (cost=0.00..0.02 rows=1 width=4) (actual time=0.015..0.023 rows=1 loops=1) > -> Function Scan on generate_series x (cost=0.00..12.50 rows=1000 width=4) (actual time=0.022..0.045 rows=10 loops=1) > Total runtime: 0.223 ms as compared to what happens when I explicitly insert the constant by hand or wrap the query up in an SQL function that takes the constant as a parameter: > Function Scan on generate_series x (cost=0.00..12.50 rows=1000 width=4) (actual time=0.027..0.054 rows=10 loops=1) > Total runtime: 0.125 ms In this trivial dummy example, it doesn't matter much. But in the kinds of complex queries you often want to use a WITH expression for, it's not appealing. If you're trying to use a WITH expression to avoid multiple evaluation of an expensive function, the gains are often consumed in the join costs. So I land up relying on wrapping things up in SQL functions instead, which is less than thrilling. >> ... so you're forced to fall back on adding it as an additional join >> expression - which isn't always reasonable or possible. > > Why not? As above for one reason. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
