Postgres - Use aliased column in subquery

Question

Is it possible to take an aliased column from the outer query and use it to filter a subquery. In essential, I'd like to do something like this

select distinct 
    array_agg(foo.id) as foo_list,
    (select sum(data) from foo where id in foo_list)
from foo
where x

but syntax error at or near "foo_list"

Answer 1

Try this:

with temp as
(
  select distinct array_agg(foo.id) as foo_list from foo
)
select sum(data) from foo 
where id in (select  foo_list from temp)