PostgreSQL函数返回Refcursor同时输出错误码与错误消息的实现方案问询

首先得明确你遇到的ERROR: function result type must be record because of OUT parameters（SQL状态42P13）的原因：当PostgreSQL函数包含OUT/INOUT参数时，函数的返回类型会被隐式定义为这些参数组成的record类型，而你原来声明返回refcursor，这就产生了类型冲突，所以报错。

要实现“成功返回游标、失败返回错误码+消息”的需求，有两种常用的可行方案：

方案1：将游标也转为OUT参数，函数返回record类型 #

这种方式直接把游标、错误码、错误消息都作为函数的输出参数，函数返回类型声明为record，逻辑清晰且符合PostgreSQL的规则。

修改后的函数代码：

CREATE OR REPLACE FUNCTION ngfcst.meta_user_preference_error_test_go(
    i_login character varying,
    i_pref_type character varying DEFAULT NULL::character varying,
    INOUT o_errcode varchar,
    INOUT o_errmsg varchar,
    OUT o_user_pref_cur refcursor
) RETURNS record 
LANGUAGE 'plpgsql' 
COST 100 
VOLATILE 
PARALLEL UNSAFE 
AS $BODY$
DECLARE 
    err_context text;
BEGIN
    -- 初始化成功状态的错误码和消息
    o_errcode := '0';
    o_errmsg := '';
    
    -- 根据参数条件打开游标
    IF i_pref_type IS NULL THEN 
        OPEN o_user_pref_cur FOR 
            SELECT * FROM ngfcst.ORG_USER_PREFERENCE WHERE login = i_login;
    ELSE 
        OPEN o_user_pref_cur FOR 
            SELECT * FROM ngfcst.ORG_USER_PREFERENCE oup 
            WHERE oup.login = i_login AND oup.pref_type = i_pref_type;
    END IF;

EXCEPTION WHEN OTHERS THEN
    -- 获取异常上下文信息
    GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;
    
    -- 设置错误码和错误消息
    o_errcode := SQLSTATE;
    o_errmsg := SQLERRM;
    
    -- 调用错误日志存储过程
    CALL ngfcst.ins_error_logs(SQLSTATE, err_context, '', '', 'ngfcst.meta_user_preference_error_test');
    
    -- 清理已打开的游标（如果存在）
    IF o_user_pref_cur IS NOT NULL THEN
        CLOSE o_user_pref_cur;
    END IF;
    o_user_pref_cur := NULL;
END;
$BODY$;

调用方式： #

调用时需要用SELECT * FROM的形式获取所有输出参数：

SELECT * FROM ngfcst.meta_user_preference_error_test_go('test_login', NULL, '', '');

调用方处理逻辑： #

1. 获取返回的o_errcode，如果不等于'0'，立即终止后续流程，根据o_errmsg处理错误；
2. 如果o_errcode为'0'，则通过FETCH从o_user_pref_cur中获取数据，使用完毕后记得关闭游标。

方案2：自定义复合类型，函数返回该复合类型 #

如果不想用多个OUT参数，可以先定义一个包含错误信息和游标的复合类型，让函数返回这个类型，代码结构更规整。

第一步：创建复合类型 #

CREATE TYPE ngfcst.user_pref_result AS (
    o_errcode varchar,
    o_errmsg varchar,
    o_user_pref_cur refcursor
);

第二步：修改函数返回该复合类型 #

CREATE OR REPLACE FUNCTION ngfcst.meta_user_preference_error_test_go(
    i_login character varying,
    i_pref_type character varying DEFAULT NULL::character varying
) RETURNS ngfcst.user_pref_result 
LANGUAGE 'plpgsql' 
COST 100 
VOLATILE 
PARALLEL UNSAFE 
AS $BODY$
DECLARE 
    result ngfcst.user_pref_result;
    err_context text;
BEGIN
    -- 初始化成功状态
    result.o_errcode := '0';
    result.o_errmsg := '';
    
    -- 打开游标
    IF i_pref_type IS NULL THEN 
        OPEN result.o_user_pref_cur FOR 
            SELECT * FROM ngfcst.ORG_USER_PREFERENCE WHERE login = i_login;
    ELSE 
        OPEN result.o_user_pref_cur FOR 
            SELECT * FROM ngfcst.ORG_USER_PREFERENCE oup 
            WHERE oup.login = i_login AND oup.pref_type = i_pref_type;
    END IF;
    
    RETURN result;

EXCEPTION WHEN OTHERS THEN
    GET STACKED DIAGNOSTICS err_context = PG_EXCEPTION_CONTEXT;
    
    -- 设置错误信息
    result.o_errcode := SQLSTATE;
    result.o_errmsg := SQLERRM;
    
    -- 记录错误日志
    CALL ngfcst.ins_error_logs(SQLSTATE, err_context, '', '', 'ngfcst.meta_user_preference_error_test');
    
    -- 清理游标
    IF result.o_user_pref_cur IS NOT NULL THEN
        CLOSE result.o_user_pref_cur;
    END IF;
    result.o_user_pref_cur := NULL;
    
    RETURN result;
END;
$BODY$;

调用方式： #

SELECT * FROM ngfcst.meta_user_preference_error_test_go('test_login', NULL);

调用方的处理逻辑和方案1一致，判断o_errcode的值即可。

注意事项 #

• 游标是会话级别的资源，使用完毕后一定要关闭，避免占用资源；
• 如果是在应用程序中调用，需要确保数据库会话保持活跃，直到游标数据被读取完毕；
• 错误码'0'是自定义的成功状态，你也可以根据需求改为其他标识，比如'SUCCESS'等。

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