目录
环境
系统平台：Microsoft Windows (64-bit) 2012
版本：4.7.6
症状
在创建数据库函数时，设定返回结果为refcursor（游标类型），将使用with的查询结果封装到游标中，编译通过，在传入参数执行函数时数据库连接产生中断。
问题原因
由于4.7.6中with机子的改进，导致与refcursor连用时会出现 unknow to 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$
#variable_conflict use_column
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
end) as dw_bz,
sum(case
when jgsy_system_code = '01' then dzq_zz
else 0
end) as dw_zz,
sum(case
when jgsy_system_code = '02' then dzq_bz
else 0
end) as rd_bz,
sum(case
when jgsy_system_code = '02' then dzq_zz
else 0
end) as rd_zz,
sum(case
when jgsy_system_code in ('03',
'0x',
'0y',
'0z') then dzq_bz
else 0
end) as zf_bz,
sum(case
when jgsy_system_code in ('03',
'0x',
'0y',
'0z') then dzq_zz
else 0
end) as zf_zz,
sum(case
when jgsy_system_code = '04' then dzq_bz
else 0
end) as zx_bz,
sum(case
when jgsy_system_code = '04' then dzq_zz
else 0
end) as zx_zz,
sum(case
when jgsy_system_code = '05' then dzq_bz
else 0
end) as mzdp_bz,
sum(case
when jgsy_system_code = '05' then dzq_zz
else 0
end) as mzdp_zz,
sum(case
when jgsy_system_code = '06' then dzq_bz
else 0
end) as qztt_bz,
sum(case
when jgsy_system_code = '06' then dzq_zz
else 0
end) 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
) f_1
where geocode like coalesce(vgeocode::text,'') || '%'
group by jgsy_system_code
)
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)
order by title);
end;
$BODY$
LANGUAGE plpgsql
由上我们可以看出,使用with后的select ...from t，此处的t就是with所构建的临时表，我们将with去掉，将t替换成with里的逻辑，如：
open
pcursor for (with
t as (
select sum(case
when jgsy_system_code = '01' then dzq_bz
else 0
end) as dw_bz,
sum(case
when jgsy_system_code = '01' then dzq_zz
else 0
end) as dw_zz,
sum(case
when jgsy_system_code = '02' then dzq_bz
else 0
end) as rd_bz,
sum(case
when jgsy_system_code = '02' then dzq_zz
else 0
end) as rd_zz,
sum(case
when jgsy_system_code in ('03',
'0x',
'0y',
'0z') then dzq_bz
else 0
end) as zf_bz,
sum(case
when jgsy_system_code in ('03',
'0x',
'0y',
'0z') then dzq_zz
else 0
end) as zf_zz,
sum(case
when jgsy_system_code = '04' then dzq_bz
else 0
end) as zx_bz,
sum(case
when jgsy_system_code = '04' then dzq_zz
else 0
end) as zx_zz,
sum(case
when jgsy_system_code = '05' then dzq_bz
else 0
end) as mzdp_bz,
sum(case
when jgsy_system_code = '05' then dzq_zz
else 0
end) as mzdp_zz,
sum(case
when jgsy_system_code = '06' then dzq_bz
else 0
end) as qztt_bz,
sum(case
when jgsy_system_code = '06' then dzq_zz
else 0
end) 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
) f_1
where geocode like coalesce(vgeocode::text,'') || '%'
group by jgsy_system_code
)
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)
替换成：
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
end) as dw_bz,
sum(case
when jgsy_system_code = '01' then dzq_zz
else 0
end) as dw_zz,
sum(case
when jgsy_system_code = '02' then dzq_bz
else 0
end) as rd_bz,
sum(case
when jgsy_system_code = '02' then dzq_zz
else 0
end) as rd_zz,
sum(case
when jgsy_system_code in ('03',
'0x',
'0y',
'0z') then dzq_bz
else 0
end) as zf_bz,
sum(case
when jgsy_system_code in ('03',
'0x',
'0y',
'0z') then dzq_zz
else 0
end) as zf_zz,
sum(case
when jgsy_system_code = '04' then dzq_bz
else 0
end) as zx_bz,
sum(case
when jgsy_system_code = '04' then dzq_zz
else 0
end) as zx_zz,
更多解决方案请登录【瀚高技术支持平台】查看瀚高技术支持平台
扫一扫
1212
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
