PostgreSQL单函数如何同时返回游标与动态生成文本

原有代码问题说明 #

你写的两个原函数存在两个致命逻辑错误：

• 第一个函数遍历完游标后执行了CLOSE关闭操作，再返回的游标是已失效的关闭状态，调用方无法读取数据
• 第二个函数声明返回类型为refcursor，但实际返回text类型字符串，函数执行时会直接抛出类型不匹配错误

符合约束的实现代码 #

方案通过OUT参数同时承载两个返回结果，全程使用游标遍历拼接字符串，通过动态SQL定义游标查询，完全满足提出的强制约束：

CREATE OR REPLACE FUNCTION get_students(
    IN classId integer,
    OUT student_cursor refcursor,
    OUT name_list text
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_dynamic_sql text;
    student record;
BEGIN
    -- 初始化返回值
    name_list := '';
    -- 动态拼接查询SQL，满足动态SQL使用要求
    v_dynamic_sql := 'SELECT firstName, surname FROM tests.students WHERE class = $1';
    -- 打开可滚动游标，通过EXECUTE执行动态SQL，传入班级参数
    OPEN student_cursor SCROLL FOR EXECUTE v_dynamic_sql USING classId;

    -- 循环遍历游标拼接学生姓名，满足必须使用游标的要求
    LOOP
        FETCH student_cursor INTO student;
        EXIT WHEN NOT FOUND;
        name_list := name_list || '  ' || student.firstName || ' ' || student.surname;
    END LOOP;

    -- 将游标指针移回结果集起始位置，保证返回的游标可被调用方正常遍历
    MOVE FIRST FROM student_cursor;

    -- 注意：不要关闭游标，关闭后返回的游标会失效，游标会在事务结束后自动释放
END;
$$;

调用方式 #

因为refcursor类型返回值仅在事务生命周期内有效，调用需要在显式事务中执行：

-- 开启事务
BEGIN;
-- 调用函数，会返回游标名和拼接好的姓名字符串两个字段
SELECT * FROM get_students(2);
-- 假设返回的游标名为<unnamed portal 1>，可通过以下语句读取游标内的全部学生数据
FETCH ALL FROM "<unnamed portal 1>";
-- 提交事务后游标自动释放
COMMIT;

关键注意点 #

• 游标必须声明为SCROLL可滚动类型，否则遍历拼接完字符串后游标指针停在结果集末尾，无法回退到起始位置供调用方读取
• 动态SQL使用USING子句传入参数，不要直接拼接参数值到SQL字符串，避免SQL注入风险
• 不要执行CLOSE student_cursor操作，关闭后返回的游标会直接失效
• 姓名拼接全程通过游标循环逐行处理，没有使用聚合函数绕过游标使用要求

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