如何返回一组jsonb对象？PostgreSQL函数返回类型错误排查

问题描述 #

我定义了如下SQL函数get_those_other_strings，接收int类型参数pid，返回text类型集合，运行正常：

create or replace function get_those_other_strings(pid int)
returns setof text
language sql
returns null on null input
as $$
    select array_agg(t1.text_column) as val
    from table1 t1
    left join table2 t2 on t1.id = t2.value_id
    where t2.other_value_id = pid
$$;

该函数通过关联表查询返回关联的text值集合。尝试创建类似函数get_those_other_jsonbs，仅将返回类型改为jsonb：

create or replace function get_those_other_jsonbs(pid int)
returns setof jsonb
language sql
returns null on null input
as $$
    select array_agg(t1.jsonb_column) as val
    from table1 t1
    left join table2 t2 on t1.id = t2.value_id
    where t2.other_value_id = pid
$$;

保存时却报错：return type mismatch in function declared to return jsonb，但单独执行函数内的查询可正常返回类似如下的jsonb数组结果：

[{"key1":"val1","key2":"val2","ke2":"val3"},{"key1":"val3","key2":"val5","key3":"val8"}]

请问我哪里出错了？

问题原因与解决方法 #

错误根源 #

你混淆了两种返回类型的逻辑：

• 第一个函数声明returns setof text，表示返回多行单个text值，但你用array_agg返回的是单个text数组——PostgreSQL对text数组做了隐式展开，自动把数组拆成多行text返回，所以没报错。
• 但jsonb数组不支持这种隐式展开，array_agg(t1.jsonb_column)返回的是jsonb[]（PostgreSQL原生数组类型），和函数声明的setof jsonb（多行单个jsonb）类型不匹配，因此触发报错。

两种可行解决方案 #

方案1：返回多行单个jsonb值

去掉array_agg，直接返回每行的jsonb_column，让返回结果和setof jsonb的声明完全匹配：

create or replace function get_those_other_jsonbs(pid int)
returns setof jsonb
language sql
returns null on null input
as $$
    select t1.jsonb_column
    from table1 t1
    left join table2 t2 on t1.id = t2.value_id
    where t2.other_value_id = pid
$$;

方案2：返回单个jsonb数组

修改函数返回类型为jsonb（去掉setof），同时用to_jsonb把PostgreSQL原生数组转换成标准jsonb数组：

create or replace function get_those_other_jsonbs(pid int)
returns jsonb
language sql
returns null on null input
as $$
    select to_jsonb(array_agg(t1.jsonb_column)) as val
    from table1 t1
    left join table2 t2 on t1.id = t2.value_id
    where t2.other_value_id = pid
$$;

补充说明 #

第一个text函数的成功是PostgreSQL的特殊隐式转换行为，并非规范写法。定义函数时一定要让返回结果的类型和声明严格一致，避免依赖这种非通用的隐式转换。

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