Idea

User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example

SELECT * FROM t1, tf(15,"foobar") AS t2 WHERE t1.a=t2.b

We might (or might not) specify that all function arguments must be constants.

UDF

• It won't be a UDF, of course, but a new plugin type.
• it'll use a generator interface

SQL

SQL Standard says

this is my understanding of the standard. it might be wrong

To define a table function one uses

CREATE FUNCTION ... RETURNS TABLE (column type [, column type ...])

It should return a table expression, which pretty much means either

  RETURN SELECT ...

or

  CREATE LOCAL TEMPORARY TABLE t1 (...)

  INSERT t1 VALUES (...)

  RETURN t1;

and it's used as

SELECT ... FROM TABLE(func(arg1, arg2, ...)) ...

Optimizations

Trivial function (with the body of only RETURN SELECT) can be treated as view. But I'm not sure it's a use case worth optimizing.

Complex function can be changed from materializing to streaming if we can be sure that every inserted row is guaranteed to be returned. That is:

• it uses exactly the second pattern above. CREATE/INSERT/RETURN.
• CREATE and RETURN are not conditional.
• No DELETE, no UPDATE, no TRUNCATE, no ALTER, etc.
• No dynamic SQL