Pass aggregate function column with self left join

I would like to call a agg开发者_JAVA技巧regate function (cost) once on a column and cascade it through the self left joins (used to create a permutation or combination of rows). That way I figure the cost is O(n) apposed to calling it after the joins O(row^joins). Starting with something like this:

id | cost 
----------
1  |  5   
2  |  10  

I would like to do something similar to below. I can do something similar with a select into a temporary table and joining off that but I would like to avoid using a temporary table...

CREATE TEMP TABLE tmp_750309_plans AS (SELECT *, cost(id) as cost  FROM plans WHERE plans.id IN (1,2,...));

SELECT * FROM tmp_750309_plans AS t1 LEFT JOIN tmp_750309_plans AS t2 ON ...

I would prefer to do something like:

SELECT id, cost(id) as cost FROM plans AS t1
LEFT JOIN t1 AS t2 
ON t1.id != t2.id
AND ...

To get something like this:

id | cost | id | cost |
-----------------------
1  |  5   |NULL| NULL |
2  |  10  |3   | 15   |

Any help would be greatly appreciated.

No need to create the temporary table, just make your SELECT a derived table:

SELECT * 
FROM (
  SELECT *, cost(id) as cost  
  FROM plans 
  WHERE plans.id IN (1,2,...)
) tmp AS t1 LEFT JOIN tmp AS t2 ON ...

or an alternative solution with a common table expression (for PostgreSQL 8.4 and above)

with tmp as (
  SELECT *, cost(id) as cost  
  FROM plans 
  WHERE plans.id IN (1,2,...)
)
SELECT *
FROM tmp as T1
  LEFT JOIN tmp AS t2 ON ...

Sounds like you need to make use of window functions see this example.

Example:

SELECT id, sum(cost) OVER (ORDER BY cost) FROM t1;