User functions (UDF-like) that return a table, rows and columns. It should be possible to use it in other statements. For example
We might (or might not) specify that all function arguments must be constants.
- It won't be a UDF, of course, but a new plugin type.
- it'll use a generator interface
this is my understanding of the standard. it might be wrong
To define a table function one uses
It should return a table expression, which pretty much means either
and it's used as
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