It is basically describing a wishlist where you can pass arbitrary sql as source for your "main" sql via valid sql itself without resolving to dynamic sql generation - presumably with support for type checks etc. Basically a level above a custom sql generator.
Notwithstanding the fact that no database supports the described approach, this combined with flexibility of sql itself could give rise to many convoluted setups. Someone builds arbitrary nesting on top of this or does complex conditional constructs - suddenly you have doubts about what is the actual sql that will be produced/executed. It is not like you can write a unit test and attach a debugger to the sql to see whats going on within the nested levels. Redirecting to temporary tables and print based debugging is only available for procedural sql which is not the target here.
What I meant by "mainly a view to view, filter and aggregate data" is that we should keep those core actions as simple as possible. Move the complexity to the application logic written in whatever language - then atleast you'll be able to test/troubleshoot it much better and gain from the existing mature approaches/tooling to do such things. Support for such things is grossly underdeveloped for sql and it gets hairy real fast when it comes to building layers with sql.
Notwithstanding the fact that no database supports the described approach, this combined with flexibility of sql itself could give rise to many convoluted setups. Someone builds arbitrary nesting on top of this or does complex conditional constructs - suddenly you have doubts about what is the actual sql that will be produced/executed. It is not like you can write a unit test and attach a debugger to the sql to see whats going on within the nested levels. Redirecting to temporary tables and print based debugging is only available for procedural sql which is not the target here.
What I meant by "mainly a view to view, filter and aggregate data" is that we should keep those core actions as simple as possible. Move the complexity to the application logic written in whatever language - then atleast you'll be able to test/troubleshoot it much better and gain from the existing mature approaches/tooling to do such things. Support for such things is grossly underdeveloped for sql and it gets hairy real fast when it comes to building layers with sql.