Use result of aggregate in same select? Is it possible to feed the result of an aggregate select into the same select with Postgresql?  

I'm aware of the WITH clause -- but, this would be true syntactical sugar, wouldn't it?

SELECT some_agg(x) as y, some_func(y) FROM z;


Is Postgres smart enough to run the aggregation once if it's written as:

SELECT some_agg(x), some_func( some_agg(x) ) FROM z;
As far as I'm aware (up to 9.2), you can not.  They may have implemented that change in 9.3 or 9.4 ... but typically you would use a CTE (WITH) or just the same aggregate function..

SELECT some_agg(x) as y, some_func(some_agg(x)) from z;

I seem to recall that postgres is smart enough to know that it can reuse the some_agg(x) without having to calculate it twice.

EDIT - Adding a CTE example

WITH my_cte AS (
  SELECT
    id,
    some_agg(x) as y
  FROM tab
  GROUP BY id
) SELECT
  id, y, some_func(y)
FROM my_cte;


The easiest way to know for sure if it calculates it more than once would be to run an EXPLAIN on the plan and see.  :)

I entered the following SQL on a local DB that I have.. Below is the SQL and Explain Plan.

explain analyze
select
  product, sum(price) as x, sum(price) as y
from ticketlines
group by product
having sum(price) &gt; 300;


You can see I have the same aggregate used 3 times ... here is the explain output:

HashAggregate  (cost=796.36..796.97 rows=49 width=16) (actual time=4.650..4.658 rows=26 loops=1)
  Filter: (sum(price) &gt; 300::double precision)
  Rows Removed by Filter: 24
  -&gt;  Seq Scan on ticketlines  (cost=0.00..692.68 rows=10368 width=16) (actual time=0.006..1.020 rows=10368 loops=1)
Total runtime: 4.699 ms


It only calculates the aggregate once.