Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Implement support for table functions #181

Open
beikov opened this issue Jan 23, 2016 · 5 comments
Open

Implement support for table functions #181

beikov opened this issue Jan 23, 2016 · 5 comments

Comments

@beikov
Copy link
Member

beikov commented Jan 23, 2016

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
Copy link
Member Author

beikov commented Jan 23, 2016

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
Copy link
Member Author

beikov commented Sep 22, 2016

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

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
Copy link
Member Author

beikov commented Oct 17, 2020

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
Copy link
Collaborator

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
Copy link
Member Author

beikov commented Jan 4, 2023

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants