PostgreSQL函数如何返回单列？自定义查询函数运行报错求助

现有代码的核心问题 #

• 运算符解析冲突：PostgreSQL中jsonb类型判断键是否存在的?运算符，会被JDBC驱动（Spring Boot JPA底层依赖）识别为预编译参数占位符，导致SQL解析失败；同时部分数据库客户端在创建函数时也会将?识别为待填充参数，打断函数创建流程。
• 冗余类型转换：表结构中related_ids本身已经定义为jsonb类型，不需要额外加::jsonb强转，属于无效代码。
• 冗余判断逻辑：?运算符本身返回布尔值，不需要额外加= true判断。

修复后的函数写法 #

我们用?运算符对应的内置底层函数jsonb_exists替代运算符即可完全规避符号冲突问题，同时可以改成更轻量的SQL函数实现，性能优于PL/pgSQL：

CREATE OR REPLACE FUNCTION find_node_id(_relation_id text)
RETURNS TABLE(node_id text) AS
$func$
    SELECT CAST(node_id AS VARCHAR) AS node_id 
    FROM node_mapping 
    WHERE jsonb_exists(related_ids, _relation_id);
$func$ LANGUAGE sql STABLE;

注：添加STABLE修饰符表示相同输入下函数返回结果固定，PostgreSQL查询优化器可以对该函数调用做性能优化，适合查询类函数使用。

额外注意事项 #

如果你的related_ids中存储的键是数字类型（而非字符串类型），需要将传入的_relation_id转为数字类型后再匹配，对应条件改为：

WHERE jsonb_exists(related_ids, _relation_id::numeric::text)

Spring Boot JPA调用示例 #

你可以在JPA Repository接口中用原生查询调用该函数，直接拿到String类型的List结果：

@Query(value = "SELECT * FROM find_node_id(:relationId)", nativeQuery = true)
List<String> findNodeIdsByRelationId(@Param("relationId") String relationId);

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