>functions that take sets as input and produce arbitrarily different sets as output
It is possible to return arbitrary sets from functions in many systems-- recent versions of the SQL standard even specify table functions (with syntax "RETURNS TABLE(columns)")[0] as well as polymorphic table functions (PTFs)[1]. And in addition to implementations of those standards, extensions like PL/[pg]SQL also have a few other ways of effecting table-like returns (parameter modes, composite type returns).
Right, I'm aware of table-returning functions and the general idea of functions as table sources in a FROM clause. I think the biggest gap is not having complementary table-consuming function signatures and ways to use functions as table sinks other than aggregate function calls.
I'd also want to allow a function to be both a table source and a table sink, which I think means that you might need a sub-query like syntax to supply the source that will be consumed by the function. This is a vague idea and not a well-defined language proposal:
CREATE OR REPLACE FUNCTION
-- invent a table-sink func signature?
func1( TABLE t1(a int, b text, c text) )
RETURNS TABLE (x int, y boolean) AS $$
-- access input set via declared name
SELECT
a, b < c
FROM t1
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION
func2( TABLE t1(a int, b boolean) )
RETURNS int AS $$
SELECT a WHERE b
$$ LANGUAGE SQL;
-- implicit positional matching of func input cols?
SELECT f1r.x, f1r.y
FROM func1( SELECT e1, e2, e3 FROM ...) AS f1r;
-- explicit input mapping to override positions?
SELECT f1r.x, f1r.y
FROM func1( SELECT e1, e2, e3 INTO b, a, c FROM ...) AS f1r;
-- function composition
SELECT func2(func1( SELECT e1, e2, e3 FROM ...));
I'd also want to allow a function to be both a table source and a table sink, which I think means that you might need a sub-query like syntax to supply the source that will be consumed by the function. This is a vague idea and not a well-defined language proposal: