PostgreSQL存储函数jsonb提取值与表字段等值匹配失效、不等式可用
问题根因
- 等值匹配失效的核心原因是jsonb取值方式错误:用单箭头
->提取jsonb字段时,返回值是jsonb类型,隐式转换为Varchar的过程中,会把jsonb字符串自带的前后双引号也作为实际字符存入变量 - 你通过notice看到的
user_email实际值是"x@x"(包含两个双引号字符),而非预期的纯文本x@x;表中存储的email是无引号的x@x,二者不相等,因此等值查询无法命中 - 你测试的不等式匹配能命中,是因为带双引号的
user_email首字符是双引号(ASCII值34),表中email值x@x的首字符是x(ASCII值120),字典序上x大于",因此不等式判断成立
修复方案
- 方案1:提取jsonb的纯文本值,将取值语句从
req->'email'改为双箭头req->>'email'——双箭头操作符会直接返回jsonb字符串去掉引号后的纯text类型,无额外字符
替换对应赋值行即可:user_email := req->>'email'; - 方案2:可以直接在WHERE子句中直接取值,无需声明中间变量,代码更简洁:
直接将查询条件改为WHERE email = req->>'email',可移除DECLARE块里的user_email变量
修正后的完整函数代码
CREATE OR REPLACE FUNCTION wg.get_user( req jsonb) RETURNS jsonb LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE user_email VARCHAR; resp JSONB; BEGIN -- 双箭头取纯文本,剔除jsonb自带的双引号 user_email := req->>'email'; raise notice '(%)', user_email; -- 此处输出将为(x@x),无额外双引号 SELECT row_to_json(t) INTO resp FROM ( SELECT email, name, default_project_id FROM wg.users WHERE email = user_email ) t; RETURN resp; END; $BODY$;
内容的提问来源于stack exchange,提问作者Miner_Glitch
相关产品推荐
相关产品推荐

