{"record":{"id":"5dc1eafd6018c2f0","repo":"hibernate/hibernate-orm","slug":"postgresql-only-supports-ref-cursor-parameters-as","errorCode":null,"errorMessage":"PostgreSQL only supports REF_CURSOR parameters as the first parameter","messagePattern":"PostgreSQL only supports REF_CURSOR parameters as the first parameter","errorType":"exception","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/PostgreSQLLegacyDialect.java","lineNumber":1163,"sourceCode":"\tpublic boolean supportsUnboundedLobLocatorMaterialization() {\n\t\treturn false;\n\t}\n\n\t@Override\n\tpublic SelectItemReferenceStrategy getGroupBySelectItemReferenceStrategy() {\n\t\treturn SelectItemReferenceStrategy.POSITION;\n\t}\n\n\n\t@Override\n\tpublic CallableStatementSupport getCallableStatementSupport() {\n\t\treturn getVersion().isSameOrAfter( 11 ) ? PostgreSQLCallableStatementSupport.INSTANCE : PostgreSQLCallableStatementSupport.V10_INSTANCE;\n\t}\n\n\t@Override\n\tpublic ResultSet getResultSet(CallableStatement statement, int position) throws SQLException {\n\t\tif ( position != 1 ) {\n\t\t\tthrow new UnsupportedOperationException( \"PostgreSQL only supports REF_CURSOR parameters as the first parameter\" );\n\t\t}\n\t\treturn (ResultSet) statement.getObject( 1 );\n\t}\n\n\t@Override\n\tpublic ResultSet getResultSet(CallableStatement statement, String name) throws SQLException {\n\t\tthrow new UnsupportedOperationException( \"PostgreSQL only supports accessing REF_CURSOR parameters by position\" );\n\t}\n\n\t@Override\n\tpublic boolean qualifyIndexName() {\n\t\treturn false;\n\t}\n\n\t@Override\n\tpublic IdentityColumnSupport getIdentityColumnSupport() {\n\t\treturn PostgreSQLIdentityColumnSupport.INSTANCE;\n\t}","sourceCodeStart":1145,"sourceCodeEnd":1181,"githubUrl":"https://github.com/hibernate/hibernate-orm/blob/fad1729dce015f908198d57a8d80274a30f905a5/hibernate-community-dialects/src/main/java/org/hibernate/community/dialect/PostgreSQLLegacyDialect.java#L1145-L1181","documentation":"PostgreSQL's server/JDBC combination only materializes a refcursor returned as the first OUT parameter of a callable statement. PostgreSQLLegacyDialect.getResultSet(CallableStatement, position) therefore requires position == 1 and throws UnsupportedOperationException for any other position when Hibernate extracts REF_CURSOR output parameters from a StoredProcedureQuery/ProcedureCall.","triggerScenarios":"A PL/pgSQL function with multiple refcursor OUT parameters, then asking Hibernate for the result set at position 2 or higher (iterating outputs / hasMoreResults-style access); StoredProcedureQuery on PostgreSQL registering more than one cursor output.","commonSituations":"Porting SQL Server/Oracle procedures that return several cursors per call; expecting PostgreSQL to behave like Oracle with multiple cursors; report screens fed by one procedure returning several result sets.","solutions":["Redesign the function to return a single refcursor in the first parameter","Return SETOF rows instead of a refcursor and call it as 'select * from func()'","Fetch additional cursors manually: (ResultSet) callableStatement.getObject(n) outside Hibernate","Access only output position 1 through Hibernate"],"exampleFix":"-- before\ncreate function read_orders(out c1 refcursor, out c2 refcursor) ... \n// Java: outputs.getOutputByPosition(2).asResultSet() -> throws\n\n-- after\ncreate function read_orders() returns setof orders ...\nList<Order> l = em.createNativeQuery( \"select * from read_orders()\", Order.class ).getResultList();","handlingStrategy":"validation","validationCode":"// PostgreSQL: Hibernate can only retrieve a REF_CURSOR at position 1\nint cursorPosition = 1;\nif ( cursorPosition != 1 ) {\n    throw new IllegalArgumentException(\n        \"Fetch this cursor manually via callableStatement.getObject(position)\" );\n}\nStoredProcedureQuery q = em.createStoredProcedureQuery( \"read_orders\" );\nq.registerStoredProcedureParameter( 1, void.class, ParameterMode.REF_CURSOR );","typeGuard":null,"tryCatchPattern":"try {\n    ResultSet rs = dialect.getResultSet( stmt, position );\n}\ncatch ( UnsupportedOperationException e ) {\n    // PostgreSQL: fall back to raw JDBC retrieval\n    ResultSet rs = (ResultSet) stmt.getObject( position );\n}","preventionTips":["Keep exactly one refcursor as the first OUT parameter of stored functions","Prefer set-returning functions queried with select over cursors","Smoke-test stored procedures against the target database in CI"],"tags":["postgresql","stored-procedure","ref-cursor","jdbc"],"backgroundTag":"stored-procedure-ref-cursor","analyzedSha":"fad1729dce015f908198d57a8d80274a30f905a5","analyzedAt":"2026-08-22T04:13:57.527Z","schemaVersion":2},"datasetVersion":"2026-08-22T09:17:25.309Z"}