Your first example, it looked like this was tried at some point see this. I bet that with a bit more effort, this problem could be resolved instead of supporting custom SQL for a whole bunch of different databases.

If you want to code a SQL function call in a screen or service, you can use the sqlFind method in EntityFacade.java line 87 for finding an entity list (just like an entity-find).

For a more general SQL implementation, it might be good to have one that can be accessed programatically. This would allow for something like what you suggested. One problem with this is that Moqui supports multiple databases that have different SQL syntax. Moqui currently handles that by making simple CRUD queries easy which is the 90% of the database query use-cases.