Hi.
What would be the best way to run native Postgres/PostGis queries from within Moqui Services.
The queries are selects on Postgres functions with parameters which return results as dynamic tables.
The tables match some Moqui Entity Definitions.
I’ve looked at the problem today and so far I replicated the SqlRunner code and manually constructed my return objects/entities.
Is there a better suited or agreed upon way?
Regards.
Hi.
As mentioned, I ended-up providing a run#NativeQuery service loosely based on the SqlRunner code and some of the EntityFacadeImpl code.
You pass the query and the Entity to which you want to map the result to and voila.
Not very cool but it works 
Thanks,
Rgds.
At some point it would be nice to have something more helpful, but for now you are correct that just like the SqlRunner.xml screen in order to have complete control over a query you have to drop down to the JDBC API, and you can get a JDBC Connection object (already enrolled in the transaction and such) with something like this:
Connection con = ec.entity.getConnection(groupName)
Note that “groupName” is a value specified in entity definitions and in the entity-facade section of the Moqui Conf XML file to allow for different entities mapped to different datasources. The default is “transactional” and that’s mostly what you’ll use, unless of course you are using multiple entity groups.