Implement support for table functions

[beikov]

There are some cases that we need to cover

• unnest from an array column like select unnest(table.arrayColumn)
• table generating functions like select * from generate_series(1,10)
• plain values like select * from (values (1, 'abc'), (2, 'def'))
• A count entity function that does a simple wrapping of a subquery select count(*) form (select * from ...) x without accessible columns

[beikov]

The only way to support that in my opinion is to register some virtual entities like done with CTEs and replace them in the generated SQL with the vendor specific syntax.

[beikov]

This might also be a nice function that we could try to integrate at least in a test to see if entity functions can be applied in that case.

http://bender.io/2016/09/18/dynamic-pivot-tables-with-json-and-postgresql/

Here are also some other resources. Note that entity functions should be able to contribute ctes

• http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm
• https://technet.microsoft.com/en-us/library/ms191165%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
• https://www.postgresql.org/docs/9.1/static/functions-srf.html
• http://stackoverflow.com/questions/806689/generate-series-equivalent-in-db2

Update 2020: The VALUES clause and subquery in FROM clause use the EntityFunction abstraction. When implementing support for unnest and generate_series we should reuse and adapt that abstraction.

[beikov]

I just had a thought that since entity array expressions are a thing now, it would be nice if we could use VALUES or generate_series like an entity array expression. I'm thinking about using a secondary entity view root like e.g. @EntityViewRoot(name = "days", expression = "generate_series(CURRENT_DATE, :param, interval('1 day'))", condition = "...") to generate a few days for e.g. grouping/aggregating. Not sure yet what kind of syntax we could introduce for actual values.

Regarding values support - we could introduce special values entity functions for some basic types e.g. values_integer, values_string which take var args, but maybe it's easier in such a case for a user to use inlined CTEs instead.

[jwgmeligmeyling]

select unnest(table.arrayColumn) already works, I suppose you mean select * from unnest(table.arrayColumn)?

How would we go about implementing the simple cases of unnest and generate_series with the existing ENTITY_FUNCTION? I believe its capabilities improved quite significantly since the inline subquery rendering / lateral join support?

[beikov]

select unnest(table.arrayColumn) already works, I suppose you mean select * from unnest(table.arrayColumn)?

Yes, exactly. Only PG is so lenient to allow using unnest in a scalar expression context. Also, other table functions require the use in the FROM clause.

How would we go about implementing the simple cases of unnest and generate_series with the existing ENTITY_FUNCTION? I believe its capabilities improved quite significantly since the inline subquery rendering / lateral join support?

Yes, I tried to model the ENTITY_FUNCTION infrastructure in a generic fashion to eventually allow users to provide custom functions, or at least make it pluggable for our own purposes, so ideally we would extract some sort of SPI out of this to allow implementing other table functions.