As you can read here, PostgreSQL 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 necessary 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, so for example can produce cummulative sums easily.