PostgreSQL调用返回refcursor函数时ResultSet排序不生效问题

复现代码与现象 #

PostgreSQL自定义函数 #

create function test(v_param bigint, OUT mycursor refcursor) returns refcursor
language plpgsql
AS $$
DECLARE
-- 变量定义省略
BEGIN
l_stmt := 'select * from (select * from mytable order by date_column DESC) as t';
-- 其余逻辑省略
OPEN mycursor FOR EXECUTE l_stmt;
END;
$$;

注：原示例存在bigin类型拼写错误、字符串使用双引号的语法问题，上述代码已做语法修正

直接执行SQL场景（排序正常） #

Java通过PreparedStatement直接执行和函数内逻辑完全一致的查询SQL：

Connection conn = sqlSession.getConnection();
PreparedStatement preStmt = conn.prepareStatement("select * from (select * from mytable order by date_column DESC) as t");
ResultSet resultSet = preStmt.executeQuery();
while(resultSet.next()) {
   /**
      date_column| etc_column
      2022/06/25 | a
      2022/06/24 | b
      2022/06/23 | c
   **/
   System.out.println(resultSet.getObject(11)); // 输出按date_column降序排列，符合预期
}

该场景下返回结果按date_column字段降序排列，顺序符合预期。

调用函数获取refcursor场景（排序异常） #

Java关闭连接自动提交后，通过CallableStatement调用自定义函数获取refcursor对应的结果集：

Connection conn = sqlSession.getConnection();
conn.setAutoCommit(false);
CallableStatement call = conn.prepareCall("{ ? = call test(?) }");
call.registerOutParameter(1, Types.REF_CURSOR);
call.setLong(2, 0L);
call.execute();
ResultSet resultSet = call.getObject(1, ResultSet.class);
while(resultSet.next()) {
    /**
      date_column| etc_column
      2022/06/24 | b
      2022/06/25 | a
      2022/06/23 | c
   **/
   System.out.println(resultSet.getObject(11)); // 结果顺序混乱，不符合降序预期
}

该场景下返回结果未按date_column字段降序排列，顺序混乱。

问题原因 #

排序失效和refcursor本身没有关系，根本原因是你的SQL写法从SQL语义上就不保证结果有序：

• 按照SQL标准，只有最外层查询携带ORDER BY子句时，返回结果的顺序才是可预期的。你把ORDER BY写在子查询里，仅能约束子查询内部的数据处理顺序，当子查询作为派生表被外层select *引用时，外层没有排序声明，数据库可以按任意顺序返回结果，不保证和子查询排序后的顺序一致。
• 直接用PreparedStatement执行时结果有序只是巧合：这个场景下PostgreSQL优化器生成执行计划时没有消除子查询的排序步骤，刚好按排序后的顺序返回了结果，但这不是数据库承诺的稳定行为，换执行路径、换数据量、换数据库版本都可能出现顺序变化。
• 走refcursor调用时顺序混乱是正常表现：PL/pgSQL执行动态SQL打开游标时，优化器生成了和直接执行不同的执行计划，识别到子查询里的ORDER BY既没有配合LIMIT做分页，外层也没有排序要求，属于无意义的冗余操作，直接把这步排序优化掉了，最终返回的结果自然就是无序的。

修复方法 #

把排序逻辑挪到最外层查询即可，不要指望子查询里的排序能传递到外层结果：

-- 正确写法，ORDER BY直接放在最外层
l_stmt := 'select * from mytable order by date_column DESC';

如果业务逻辑必须在子查询内排序（比如子查询中需要做分页、计算窗口函数），那外层查询也要写完全一致的ORDER BY规则，才能保证最终返回的结果顺序符合预期。

顺便提两个示例代码里的明显问题：

• 函数定义里参数类型写的bigin是笔误，正确类型是bigint；PL/pgSQL里字符串常量要用单引号包裹，原示例用的双引号会被识别成标识符，直接执行会报错。
• Java调用存储过程时，第一个?是注册的refcursor出参，传入的业务入参对应第二个占位符，应该写call.setLong(2, 0L)，原示例写的call.setLong(1,0L)会覆盖出参配置，调用本身就会抛参数不匹配异常。

内容的提问来源于stack exchange，提问作者Ju Sung Park