本文共 8705 字，大约阅读时间需要 29 分钟。

在创建数据库函数时，如果您选择返回结果为refcursor（游标类型），并使用with的查询结果封装到游标中，编译通过，但在传入参数执行函数时数据库连接产生中断。这种问题通常与PostgreSQL版本有关。

问题原因

在PostgreSQL 4.7.6版本中，with子句的优化可能会导致与refcursor一起使用时出现隐式转换为text类型的问题。这种转换可能会影响函数的正常执行，导致连接中断。

解决方案

解决此问题有两种方式：

方式一

升级数据库到4.7.7或降级到4.3.2即可解决此问题。

方式二

改写游标与with语句的使用方式。以下是一个示例：

原函数代码如下：

CREATE OR REPLACE FUNCTION proc_sssj_dzq_bz(    IN var_geocode character varying,    OUT pcursor refcursor)RETURNS refcursor AS$BODY$declare    vgeocode varchar(6);begin    if var_geocode = '100000' then        vgeocode := ''::text;    elsif substr(var_geocode, 3, 4) = '0000' then        vgeocode := substr(var_geocode, 1, 2);    elsif substr(var_geocode, 5, 2) = '00' then        vgeocode := substr(var_geocode, 1, 4);    else        vgeocode := var_geocode;    end if;    open pcursor for (        with t as (            select sum(                case                when jgsy_system_code = '01' then dzq_bz                else 0            ) as dw_bz,            sum(                case                when jgsy_system_code = '01' then dzq_zz                else 0            ) as dw_zz,            sum(                case                when jgsy_system_code = '02' then dzq_bz                else 0            ) as rd_bz,            sum(                case                when jgsy_system_code = '02' then dzq_zz                else 0            ) as rd_zz,            sum(                case                when jgsy_system_code in ('03', '0x', '0y', '0z') then dzq_bz                else 0            ) as zf_bz,            sum(                case                when jgsy_system_code in ('03', '0x', '0y', '0z') then dzq_zz                else 0            ) as zf_zz,            sum(                case                when jgsy_system_code = '04' then dzq_bz                else 0            ) as zx_bz,            sum(                case                when jgsy_system_code = '04' then dzq_zz                else 0            ) as zx_zz,            sum(                case                when jgsy_system_code = '05' then dzq_bz                else 0            ) as mzdp_bz,            sum(                case                when jgsy_system_code = '05' then dzq_zz                else 0            ) as mzdp_zz,            sum(                case                when jgsy_system_code = '06' then dzq_bz                else 0            ) as qztt_bz,            sum(                case                when jgsy_system_code = '06' then dzq_zz                else 0            ) as qztt_zz        from (            select v.*,             case            when v.jgsy_ifvertical = '1' then '008'            when jgsy_system_code = '0x' then coalesce(zhxsh::text,'') || '201'            when jgsy_system_code = '0y' then coalesce(zhxsh::text,'') || '202'            when jgsy_system_code = '0z' then coalesce(zhxsh::text,'') || '203'            else zhxsh            end as zhxsh            from v_bzzz v,            geocode g            where v.geocode = g.code            and geocode like coalesce(vgeocode::text,'') || '%'            group by jgsy_system_code        ) f_1    );    select title,    coalesce(sum(hj), 0) as hj,    coalesce(sum(dw), 0) as dw,    coalesce(sum(rd), 0) as rd,    coalesce(sum(zf), 0) as zf,    coalesce(sum(zx), 0) as zx,    coalesce(sum(mzdp), 0) as mzdp,    coalesce(sum(qztt), 0) as qztt    from (        select 'bz' as title,        sum(dw_bz + rd_bz + zf_bz + zx_bz + mzdp_bz + qztt_bz) as hj,        sum(dw_bz) as dw,        sum(rd_bz) as rd,        sum(zf_bz) as zf,        sum(zx_bz) as zx,        sum(mzdp_bz) as mzdp,        sum(qztt_bz) as qztt        from t    ) union all (        select 'zz' as title,        -sum(dw_zz + rd_zz + zf_zz + zx_zz + mzdp_zz + qztt_zz) as hj,        -sum(dw_zz) as dw,        -sum(rd_zz) as rd,        -sum(zf_zz) as zf,        -sum(zx_zz) as zx,        -sum(mzdp_zz) as mzdp,        -sum(qztt_zz) as qztt        from t    ) union all (        select 'bz' as title,        sum(bz::numeric) as hj,        0,        0,        sum(bz::numeric) as zf,        0,        0,        0 as shengji_dfp        from v_tongji_dfpbz a        where a.dep_code like coalesce(vgeocode::text,'') || '%'        and (a.jgsy_system_code in ('xz_dfp', 'cg_dfp'))    ) f_1    group by rollup(title);end;$BODY$LANGUAGE plpgsql

改写后的函数代码如下：

CREATE OR REPLACE FUNCTION proc_sssj_dzq_bz(    IN var_geocode character varying,    OUT pcursor refcursor)RETURNS refcursor AS$BODY$declare    vgeocode varchar(6);begin    if var_geocode = '100000' then        vgeocode := ''::text;    elsif substr(var_geocode, 3, 4) = '0000' then        vgeocode := substr(var_geocode, 1, 2);    elsif substr(var_geocode, 5, 2) = '00' then        vgeocode := substr(var_geocode, 1, 4);    else        vgeocode := var_geocode;    end if;    open pcursor for (        select title,        coalesce(sum(hj), 0) as hj,        coalesce(sum(dw), 0) as dw,        coalesce(sum(rd), 0) as rd,        coalesce(sum(zf), 0) as zf,        coalesce(sum(zx), 0) as zx,        coalesce(sum(mzdp), 0) as mzdp,        coalesce(sum(qztt), 0) as qztt        from (            select 'bz' as title,            sum(dw_bz + rd_bz + zf_bz + zx_bz + mzdp_bz + qztt_bz) as hj,            sum(dw_bz) as dw,            sum(rd_bz) as rd,            sum(zf_bz) as zf,            sum(zx_bz) as zx,            sum(mzdp_bz) as mzdp,            sum(qztt_bz) as qztt            from (select sum(case                when jgsy_system_code = '01' then dzq_bz                else 0) as dw_bz,                sum(case                when jgsy_system_code = '01' then dzq_zz                else 0) as dw_zz,                sum(case                when jgsy_system_code = '02' then dzq_bz                else 0) as rd_bz,                sum(case                when jgsy_system_code = '02' then dzq_zz                else 0) as rd_zz,                sum(case                when jgsy_system_code in ('03', '0x', '0y', '0z') then dzq_bz                else 0) as zf_bz,                sum(case                when jgsy_system_code in ('03', '0x', '0y', '0z') then dzq_zz                else 0) as zf_zz,                sum(case                when jgsy_system_code = '04' then dzq_bz                else 0) as zx_bz,                sum(case                when jgsy_system_code = '04' then dzq_zz                else 0) as zx_zz,                sum(case                when jgsy_system_code = '05' then dzq_bz                else 0) as mzdp_bz,                sum(case                when jgsy_system_code = '05' then dzq_zz                else 0) as mzdp_zz,                sum(case                when jgsy_system_code = '06' then dzq_bz                else 0) as qztt_bz,                sum(case                when jgsy_system_code = '06' then dzq_zz                else 0) as qztt_zz            from (select v.*,                 case                when v.jgsy_ifvertical = '1' then '008'                when jgsy_system_code = '0x' then coalesce(zhxsh::text,'') || '201'                when jgsy_system_code = '0y' then coalesce(zhxsh::text,'') || '202'                when jgsy_system_code = '0z' then coalesce(zhxsh::text,'') || '203'                else zhxsh                end as zhxsh                from v_bzzz v,                geocode g                where v.geocode = g.code                and geocode like coalesce(vgeocode::text,'') || '%'                group by jgsy_system_code            ) f_1        ) t    );    select title,    coalesce(sum(hj), 0) as hj,    coalesce(sum(dw), 0) as dw,    coalesce(sum(rd), 0) as rd,    coalesce(sum(zf), 0) as zf,    coalesce(sum(zx), 0) as zx,    coalesce(sum(mzdp), 0) as mzdp,    coalesce(sum(qztt), 0) as qztt    from ((select 'bz' as title,        sum(dw_bz + rd_bz + zf_bz + zx_bz + mzdp_bz + qztt_bz) as hj,        sum(dw_bz) as dw,        sum(rd_bz) as rd,        sum(zf_bz) as zf,        sum(zx_bz) as zx,        sum(mzdp_bz) as mzdp,        sum(qztt_bz) as qztt        from t    ) union all (        select 'zz' as title,        -sum(dw_zz + rd_zz + zf_zz + zx_zz + mzdp_zz + qztt_zz) as hj,        -sum(dw_zz) as dw,        -sum(rd_zz) as rd,        -sum(zf_zz) as zf,        -sum(zx_zz) as zx,        -sum(mzdp_zz) as mzdp,        -sum(qztt_zz) as qztt        from t    ) union all (        select 'bz' as title,        sum(bz::numeric) as hj,        0,        0,        sum(bz::numeric) as zf,        0,        0,        0 as shengji_dfp        from v_tongji_dfpbz a        where a.dep_code like coalesce(vgeocode::text,'') || '%'        and (a.jgsy_system_code in ('xz_dfp', 'cg_dfp'))    ) f_1    group by rollup(title);end;$BODY$LANGUAGE plpgsql

总结

通过上述改写，可以避免with子句与refcursor一起使用时的隐式转换问题。建议根据实际情况选择合适的PostgreSQL版本，并在必要时调整函数代码以确保最佳性能。