As you can read here, PostgreSQL 8.4 will contain so called window functions which is part of the SQL 2008 specification. Now that I know how this feature is named, I know what I was missing for the past 10 years. Window functions allow you to produce aggregated values from each row over a specific set of rows (a partition).
Assuming a table staff defined as
create table staff ( name varchar(30), dept varchar(30), salaray int );
we can now show each staff person together with the average salary of the department he/she belongs to using a very simple SQL statement like
select name, dept, salary, avg(salary) over (partition by dept) from staff;
Without using window functions, the SQL statement is considerably more complex and requires ugly subselects and joins:
select s.name, s.dept, s.salary, a.avg_salary from staff s, (select dept, avg(salary) as avg_salary from staff group by dept) as a where s.dept = a.dept
Window functions can do a lot more, for example they can produce cummulative sums easily.