JPA 2.1 StoredProcedureQuery with PostgreSQL and REF_CURSORs

Question

I have a function I created in my PostgreSQL DB that I want to call using JPA 2.1's StoredProcedureQuery method.

Here is my PostgreSQL query:

CREATE OR REPLACE FUNCTION get_values(date text) returns refcursor 
AS $$ 
    DECLARE tuples refcursor; 
    BEGIN OPEN tuples FOR 
        SELECT user, COUNT(*) 
        FROM my_table 
        WHERE date_ = date
        GROUP BY user; 
    return tuples; 
    END; 
$$ 
LANGUAGE plpgsql

This is just a simple query to count users on a particular day. This is just a demo query to test how the StoredProcedureQueries work. And in fact, it works just fine when used via postgreSQL alone.

Now, let's try and call this using JPA 2.1 and in Javaland:

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("get_values");
storedProcedure.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter(1, Object.class, ParameterMode.REF_CURSOR);
storedProcedure.setParameter(2, "2015-02-01");
storedProcedure.execute();

When I do this, I get back the following exception:

org.hibernate.HibernateException: PostgreSQL supports only one REF_CURSOR parameter, but multiple were registered

There is only a single ref cursor declared! In fact, if I just register the single REF_CURSOR parameter and hardcode in a value for my Postgresql function for the WHERE date_ = date, this call works just fine.

So it would seem adding any additional parameters to a storedprocedurequery with a ref_cursor breaks the functionality. Alone, the ref_cursor parameters works fine.

Anybody see why this would happen?? Why is it that adding parameters to the StoredProcedureQuery for my PostgreSQL function breaks it?

Example of when it works:

 CREATE OR REPLACE FUNCTION get_values(date text) returns refcursor 
AS $$ 
    DECLARE tuples refcursor; 
    BEGIN OPEN tuples FOR 
        SELECT user, COUNT(*) 
        FROM my_table 
        WHERE date_ = '2015-02-01'
        GROUP BY user; 
    return tuples; 
    END; 
$$ 
LANGUAGE plpgsql

and in javaland:

StoredProcedureQuery storedProcedure =    em.createStoredProcedureQuery("get_values");
storedProcedure.registerStoredProcedureParameter(1, Object.class, ParameterMode.REF_CURSOR);
storedProcedure.execute();

Answer 1

Short answer: Reverse the order of your two calls to registerStoredProcedureParameter():

storedProcedure.registerStoredProcedureParameter(1, Object.class, ParameterMode.REF_CURSOR);
storedProcedure.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);

Long answer: I did some digging in the Hibernate source code for Postgress callable statement support, and found that each registerStoredProcedureParameter() call creates a ParameterRegistrationImplementor instance that gets tacked into a list and passed around. You'll note that this class stores the position of the parameter, which is independent of its position within the list.

Later, this list is analyzed and assumes that the REF_CURSOR parameter will be first in line, and throws your error message if a REF_CURSOR parameter is not first, regardless of what the parameter number is.

Not a very bright way of doing things (IMHO), but at least the workaround is easy: if you swap the order of your calls, you should be fine.