Is Postgresql INOUT REFCURSOR Supported?

Does latest hibernate codebase supports INOUT REFCURSOR as parameter to a function?
I tried below function to execute by putting it in org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest

CREATE OR REPLACE FUNCTION public.cursor_func(
	INOUT c1 refcursor)
    RETURNS refcursor   
AS $$
  BEGIN    OPEN c1 FOR SELECT * FROM phone;    END; 
$$ LANGUAGE plpgsql;;

And it throws below exception:

org.hibernate.NotYetImplementedFor6Exception: org.hibernate.procedure.internal.ProcedureParameterImpl
	at app//org.hibernate.procedure.internal.ProcedureParameterImpl.getParameterBinder(
	at app//org.hibernate.procedure.internal.ProcedureParameterImpl.toJdbcParameterRegistration(
	at app//org.hibernate.procedure.internal.PostgresCallableStatementSupport.interpretCall(
	at app//org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(
	at app//org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(
	at app//org.hibernate.procedure.internal.ProcedureCallImpl.outputs(
	at app//org.hibernate.procedure.internal.ProcedureCallImpl.execute(
	at app//org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest.lambda$testStoredProcedureINOUTRefCursor$25(
	at app//org.hibernate.testing.transaction.TransactionUtil.doInJPA(
	at app//org.hibernate.testing.transaction.TransactionUtil.doInJPA(
	at app//org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest.testStoredProcedureINOUTRefCursor(
	at java.base@17.0.2/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base@17.0.2/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(
	at java.base@17.0.2/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(
	at java.base@17.0.2/java.lang.reflect.Method.invoke(
	at app//org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(
	at app//
	at app//org.junit.runners.model.FrameworkMethod.invokeExplosively(
	at app//org.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(
	at app//org.junit.internal.runners.statements.InvokeMethod.evaluate(
	at app//org.junit.internal.runners.statements.RunBefores.evaluate(
	at app//org.junit.internal.runners.statements.RunAfters.evaluate(
	at app//org.junit.internal.runners.statements.FailOnTimeout$
	at app//org.junit.internal.runners.statements.FailOnTimeout$
	at java.base@17.0.2/
	at java.base@17.0.2/

Is REFCURSOR as INOUT parameter to function in the roadmap? How can we provide custom support for functions taking REFCURSOR as INOUT parameter?


Hey, thanks for trying this out. We don’t have it on our radar right now, but we would be very grateful if you could create a JIRA issue for this, contribute the test you wrote and maybe even work on a fix for this :slight_smile:

Sure. Let me refine the test case little further and start planning to work on this.

Thanks for your reply.

@beikov INOUT refcursor not supported by hibernate-core itself or it is something for which we need to modify relevant PostgresDialect?

I don’t know what you have to modify exactly, but Hibernate generally does support INOUT parameters. It seems PostgreSQL is special in that regard though and requires some extra work.

Hi @beikov, I am trying to add test case to org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest. Before add that I ran the single test case method and found the test cases are being ignored. I was expecting the execution of test case as there is no @ignore annotation on neither class level nor on method level.
Is it being set somewhere else in any its parent classes?

How did you run the test? It’s only skipped if you don’t run it with the proper dialect. See the readme for details about how to run tests: GitHub - hibernate/hibernate-orm: Hibernate's core Object/Relational Mapping functionality

Below is how I am running.
./gradlew clean build
./gradlew setDataBase -Pdb=pgsql
cd hibernate-core
../gradlew test --tests org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest.testFunctionWithJDBCByName

PostgreSQLStoredProcedureTest is by default set to run using PostgreSQLDialect ( @RequiresDialect(PostgreSQLDialect.class)

NOTE: I am not running it using docker, rather I have local running postgresql.

The you should be running just the following:

./gradlew :hibernate-core:test --tests org.hibernate.orm.test.procedure.PostgreSQLStoredProcedureTest.testFunctionWithJDBCByName -Pdb=pgsql

When I change value for @RequiresDialect to PostgresPlusDialect, it skips the test but with PostgreSQLDialect it works fine.

How hibernate-orm checks if provided dialect is proper or not?

I need to make PostgreSQLStoredProcedureTest not to skip tests with PostgresPlusDialect.