Estoy intentando usar ENTITY MANAGER para devolver un resultado JSON de función/procedimiento almacenado de POSTGRESQL 9.6. ESTO DEVUELVE 1 REGISTRO SOLAMENTE.

 **POSTGRESQL TABLES** TABLE: **customer** [enter image description here][1] TABLE: **names** [enter image description here][2] TABLE: **address** [enter image description here][3] **POSTGRESQL STORED PROCEDURE** CREATE OR REPLACE FUNCTION jsp_member_main( IN cid numeric, OUT t json) RETURNS json LANGUAGE 'sql' COST 100.0 VOLATILE AS $function$ select json_agg(t) from (SELECT c.id customer_id, n.first_name, n.last_name, a.street1, a.city, a.state, a.postal_code FROM customer c LEFT OUTER JOIN names n ON (c.name_id = n.id) LEFT OUTER JOIN address a ON (c.id = a.customer_id) WHERE c.id=cid ) t; $function$ **POSTGRESQL STORED PROCEDURE QUERY** select jsp_member_main(99964); **RETURNS VIA SQL** [{"customer_id":99964,"first_name":"JOHN","last_name":"SMITH","street1":"123 W. MAIN ST.","city":"SPINDALE","state":"MD","postal_code":"21791"}]

Hasta aquí todo bien. Ahora trato de usar ENTITY MANAGER para devolver resultados:

 @GET @Path("SP Member Main") @Produces({"application/xml", "application/json"}) public JSONObject memberMain() throws JSONException { EntityManager em = getEntityManager(); StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("jsp_member_main"); storedProcedure.registerStoredProcedureParameter("cid", Integer.class, ParameterMode.IN); storedProcedure.registerStoredProcedureParameter("t", JSONObject.class, ParameterMode.OUT); storedProcedure.setParameter("cid", 99964); storedProcedure.execute(); JSONObject retval = (JSONObject) storedProcedure.getOutputParameterValue("t"); return retval; }

Independientemente de la clase de tipo de retorno (OUT) que intente, aparece el siguiente error:

 ***javax.ejb.EJBException root cause javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: org.postgresql.util.PSQLException: A CallableStatement function was executed and the out parameter 1 was of type java.sql.Types=1111 however type java.sql.Types=12 was registered. Error Code: 0 Call: {?= CALL ross.jsp_member_main(?)} bind => [2 parameters bound] Query: ResultSetMappingQuery()***

Lo intenté como clase String, volviendo a String, JSONArray cobverting JSONObject a JSONArray, java OBJECT, etc. Nada funciona.

¿Hay alguna manera a través de ENTITY MANAGER para devolver json desde un procedimiento/función almacenado de Postgresql? Muchas gracias por su consideración.