Stop trying to fix SQL. Stop trying to make it do things that belong with a full fledged unit testable language. It is mainly a way to view, filter and aggregate data - nothing more. Having dealt with untestable thousand lines stored procedures (each of which are copy of a similar stored procedure with subtle variations), just don't put yourself in a world of pain by doing overcomplicated things at the database end.
And when you want to query data, use a view so that a) you don't need to deal to idiosyncrasies of your ORM deciding to do weird/suboptimal joins b) if tomorrow you have to make database side changes or optimize your view, all you need to care about is to keep the select clause the same c) you can just query/profile the view and be assured that what you see is what your application will see too (in general). But don't keep nesting them like mad and expect the database to do magic.
There's a need to do complex data processing (not sure if it falls under your "view, filter and aggregate data - nothing more"), and it's an open question whether "full fledged" languages (presumably with an API, like pandas, ibis or spark), improved SQL, or another language focused on data transformation will be a better fit for this task.
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.
And when you want to query data, use a view so that a) you don't need to deal to idiosyncrasies of your ORM deciding to do weird/suboptimal joins b) if tomorrow you have to make database side changes or optimize your view, all you need to care about is to keep the select clause the same c) you can just query/profile the view and be assured that what you see is what your application will see too (in general). But don't keep nesting them like mad and expect the database to do magic.