Use result of aggregate in same select?
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.