PostgreSQL 14迁移多结果集方案时refcursor附近语法错误求助

错误原因 #

你遇到的语法错误核心原因是函数声明的语言类型和实际使用的语法不匹配：
你编写的代码中用到了DECLARE变量声明、IF分支判断、OPEN游标操作等过程化语法，这是PostgreSQL专属的PL/pgSQL过程语言的特性，但你在函数定义时指定的是language sql，标准SQL语言的函数不支持这类过程化语法，因此触发语法报错。

修正方案1：调整函数语言类型 #

仅需要将函数定义中的language sql修改为language plpgsql即可解决报错，修正后的完整代码如下：

create or replace function divide(
  a integer,
  b integer
)
returns setof refcursor
language plpgsql as
$$
  declare
    ref1 refcursor;
    ref2 refcursor;
    ref3 refcursor;
    error int := 0;
  begin
    -- 名称到索引的映射表
    open ref1 for select
      1 as result,
      2 as error;
    return next ref1;

    -- 计算结果集
    if b = 0 then
      error := 1;
      open ref2 for select
        null as result;
    else
      open ref2 for select
        a / b as result;
    end if;
    return next ref2;

    -- 错误码结果集
    open ref3 for select
      error as error;
    return next ref3;
  end;
$$;

函数调用注意事项 #

返回refcursor的函数需要在事务中读取结果，调用示例如下：

-- 开启事务
BEGIN;
-- 调用函数获取游标列表
SELECT * FROM divide(91,13);
-- 依次读取游标结果，游标名以实际返回的portal名称为准
FETCH ALL IN "<unnamed portal 1>"; -- 映射表结果
FETCH ALL IN "<unnamed portal 2>"; -- 计算结果
FETCH ALL IN "<unnamed portal 3>"; -- 错误码结果
-- 提交事务
COMMIT;

修正方案2：改用存储过程（更适配原SQL Server使用习惯） #

如果不需要用函数，更推荐直接使用PostgreSQL的存储过程实现，和你原有SQL Server的逻辑、调用方式完全一致，不需要额外处理游标，客户端可以直接获取多个结果集：

create or replace procedure divide(
  a integer,
  b integer
)
language plpgsql
as $$
declare
  error int := 0;
begin
  -- 名称到索引的映射表
  select 1 as result, 2 as error;
  -- 计算结果
  if b = 0 then
    error := 1;
    select null as result;
  else
    select a / b as result;
  end if;
  -- 错误码
  select error as error;
end;
$$;

调用方式和SQL Server几乎一致：

call divide(91, 13);

内容的提问来源于stack exchange，提问作者kol