(select query)/relation as first class citizen

Hello,

Just a bit of background - I currently work as a full-time db developer,
mostly with Ms Sql server but I like Postgres a lot, especially because I
really program in sql all the time and type system / plpgsql language of
Postgres seems to me more suitable for actual programming then t-sql.

Here's the problem - current structure of the language doesn't allow to
decompose the code well and split calculations and data into different
modules.

For example. Suppose I have a table employee and I have a function like
this (I'll skip definition of return types for the sake of simplicity):

create function departments_salary ()
returns table (...)
as
return $$
select department, sum(salary) as salary from employee group by
department;
$$;

so that's fine, but what if I want to run this function on filtered
employee? I can adjust the function of course, but it implies I can predict
all possible filters I'm going to need in the future.
And logically, function itself doesn't have to be run on employee table,
anything with department and salary columns will fit.
So it'd be nice to be able to define the function like this:

create function departments_salary(_employee query)
returns table (...)
as
return $$
select department, sum(salary) as salary from _employee group by
department;
$$;

and then call it like this:

declare _employee query;
...
_poor_employee = (select salary, department from employee where salary <
1000);
select * from departments_salary( _poor_employee);

And just to be clear, the query is not really invoked until the last line,
so re-assigning _employee variable is more like building query expression.

As far as I understand the closest way to do this is to put the data into
temporary table and use this temporary table inside of the function. It's
not exactly the same of course, cause in case of temporary tables data
should be transferred to temporary table, while it will might be filtered
later. So it's something like array vs generator in python, or List vs
IQueryable in C#.

Adding this functionality will allow much better decomposition of the
program's logic.
What do you think about the idea itself? If you think the idea is worthy,
is it even possible to implement it?

Regards,
Roman Pekar