如何将有界游标传入PostgreSQL函数？求高效实现方案

原函数问题修正 #

你的原函数存在语法与逻辑错误，修正后的版本如下：

CREATE FUNCTION public.try_ref_cursor(IN rc refcursor)
RETURNS bigint
LANGUAGE plpgsql
AS $BODY$
declare
    _rec record;
    _i integer := 0;
begin
    -- 外部游标已打开，直接循环读取
    loop
        fetch rc into _rec;
        exit when not found;
        raise notice '%', _rec;
        _i := _i + 1;
    end loop;
    return _i;
end;
$BODY$;

调用时需确保游标已初始化并打开：

declare
    mycursor refcursor;
    mycount integer;
begin
    open mycursor for select a, b, c from sometable;
    mycount := public.try_ref_cursor(mycursor);
    close mycursor;
end;

针对需求的高效解决方案 #

你的核心需求是处理不同表中结构一致的固定字段数据，完成校验聚合后返回jsonb，以下是三个高效方案：

方案一：多态函数+表继承（推荐）

如果所有目标表共享固定字段（如id、name、status），可通过表继承统一结构，再用多态函数处理：

1. 创建基础父表（仅定义公共字段）

CREATE TABLE base_entity (
    id int,
    name text,
    status boolean
);

2. 业务表继承父表

CREATE TABLE table_a (extra_col_a text) INHERITS (base_entity);
CREATE TABLE table_b (extra_col_b int) INHERITS (base_entity);

3. 编写多态处理函数

CREATE FUNCTION process_entity_data(p_entity anyelement)
RETURNS jsonb
LANGUAGE plpgsql
AS $BODY$
declare
    result jsonb := '[]'::jsonb;
    _rec record;
begin
    -- 动态查询传入表的公共字段数据
    for _rec in execute format('SELECT id, name, status FROM %s', pg_typeof(p_entity)) loop
        -- 校验逻辑：跳过status为空的记录
        if _rec.status is null then
            raise warning 'Invalid status for id %', _rec.id;
            continue;
        end;
        -- 聚合为jsonb数组
        result := result || jsonb_build_object(
            'id', _rec.id,
            'name', _rec.name,
            'valid', true
        );
    end loop;
    return result;
end;
$BODY$;

调用方式：

SELECT process_entity_data(null::table_a);
SELECT process_entity_data(null::table_b);

该方案利用PostgreSQL原生查询优化，避免数据拷贝，效率最高，同时符合接口式的统一处理逻辑。

方案二：动态SQL传入查询语句

若无法使用表继承，可直接传入查询字符串，通过动态SQL处理（需确保查询来源安全，避免注入）：

CREATE FUNCTION process_query(p_query text)
RETURNS jsonb
LANGUAGE plpgsql
AS $BODY$
declare
    result jsonb := '[]'::jsonb;
    _rec record;
begin
    for _rec in execute p_query loop
        -- 校验逻辑：仅保留status为布尔值的记录
        if _rec.status not in (true, false) then
            continue;
        end;
        -- 聚合数据
        result := result || jsonb_build_object(
            'id', _rec.id,
            'name', _rec.name,
            'status', _rec.status
        );
    end loop;
    return result;
end;
$BODY$;

调用方式：

SELECT process_query('SELECT id, name, status FROM table_a WHERE id < 100');
SELECT process_query('SELECT id, name, status FROM table_b WHERE name LIKE ''test%''');

灵活性极强，只要查询返回指定固定字段即可，效率接近原生SQL。

方案三：流式游标处理

若需处理超大数据集，可使用游标流式读取，降低内存占用：

CREATE FUNCTION process_cursor(IN rc refcursor)
RETURNS jsonb
LANGUAGE plpgsql
AS $BODY$
declare
    result jsonb := '[]'::jsonb;
    _rec record;
begin
    loop
        fetch rc into _rec;
        exit when not found;
        
        -- 校验逻辑：跳过id为空的记录
        if _rec.id is null then
            raise notice 'Skipping record with null id';
            continue;
        end;
        
        -- 聚合为jsonb
        result := result || jsonb_build_object(
            'id', _rec.id,
            'name', _rec.name,
            'status', _rec.status
        );
    end loop;
    return result;
end;
$BODY$;

调用方式：

declare
    cur refcursor;
    output jsonb;
begin
    open cur for SELECT id, name, status FROM table_a;
    output := process_cursor(cur);
    close cur;
    raise notice 'Result: %', output;
end;

适合大数据量场景，内存占用低，处理效率稳定。

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