我正在尝试检查在PL / pgSQL函数中作为参数发送的JSON中是否存在密钥。
这是功能。
CREATE FUNCTION sp_update_user(user_info json) RETURNS json
LANGUAGE plpgsql
AS $$
BEGIN
PERFORM user_info->>'lastname';
IF FOUND
THEN
UPDATE users SET lastname = user_info->>'lastname' WHERE id = sp_update_user.user_id;
END IF;
PERFORM user_info->>'firstname';
IF FOUND
THEN
UPDATE users SET firstname = user_info->>'firstname' WHERE id = sp_update_user.user_id;
END IF;
RETURN row_to_json(row) FROM (SELECT true AS success) row;
END;$$;
我尝试使用PERFORM
-clause,但是即使json键不存在,该IF FOUND
子句中的语句也会执行。
我试图PERFORM user_info->>'firstname' INTO myvar;
检查变量内容,但会触发错误ERROR: query "SELECT user_info->>'firstname' INTO myvar" is not a SELECT
。
如何使用PL / pgSQL检查JSON中是否存在JSON密钥?
您已经发现可以测试表达式user_info->>'username'
为NULL。但是您的功能仍然非常低效。而且仍然存在歧义。
对于多列重复更新一行是昂贵的。Postgres为每次更新编写一个新的行版本。使用一个单一的 UPDATE
,如果在所有可能的:
CREATE OR REPLACE FUNCTION sp_update_user(_user_id int, _user_info json)
RETURNS json AS
$func$
BEGIN
UPDATE users u
SET firstname = COALESCE(_user_info->>'firstname', u.firstname)
, lastname = COALESCE(_user_info->>'lastname' , u.lastname)
WHERE id = sp_update_user._user_id
AND ((_user_info->>'firstname') IS NOT NULL OR
(_user_info->>'lastname') IS NOT NULL);
IF FOUND THEN
RETURN '{"success":true}'::json;
ELSE
RETURN '{"success":false}'::json;
END IF;
END
$func$ LANGUAGE plpgsql;
呼叫:
SELECT sp_update_user(123, '{"firstname": "jon", "lastname": "doe"}')
对于多列,这实际上要快得多,因为仅执行一次UPDATE
(最多)。如果该WHERE
子句的结果不为true
,则根本不会进行任何更新,您将得到'{"success":false}'
结果。
如果有时表中的值已经是要更改的值,则可以进行另一种优化。考虑以下相关答案的最后一段:
user_id
原始变量中缺少变量/参数。
仍然存在一个极端的模棱两可的情况。如果该元素存在并将其设置为JSONnull
,则还将得到一条SQLNULL
作为结果。考虑:
SELECT ('{"b": null}'::json->>'b') IS NULL AS b_is_null
, ('{"c": 2}'::json->>'b') IS NULL AS b_missing;
不知道为什么要将数据类型json
用作返回类型,我只是保留了这一点。但是,如果函数未更新,则无法确定为什么得到false
。可能没有与给定id
键名的行,'firstname'
并且'lastname'
可能会丢失-或者是null
...
Postgres 9.4中有一个干净简单的解决方案,它带有“ existence”运算符-甚至可以为更大的表使用索引(与您的函数无关):jsonb
?
SELECT ('{"b": null}'::jsonb ? 'b') AS b_is_null
, ('{"c": 2}'::jsonb ? 'b') AS b_missing;
和?|
和?&
变体可以一次检查多个键。
这样我们就可以实现:
CREATE OR REPLACE FUNCTION sp_update_user(_user_id int, _user_info jsonb)
RETURNS jsonb AS
$func$
BEGIN
UPDATE users u
SET firstname = CASE WHEN _user_info ? 'firstname' THEN _user_info->>'firstname' ELSE u.firstname END
, lastname = CASE WHEN _user_info ? 'lastname' THEN _user_info->>'lastname' ELSE u.lastname END
WHERE id = sp_update_user._user_id
AND _user_info ?| '{firstname,lastname}';
IF FOUND THEN
RETURN '{"success":true}'::jsonb;
ELSE
RETURN '{"success":false}'::jsonb;
END IF;
END
$func$ LANGUAGE plpgsql;
这些调用现在可以正常工作:
SELECT sp_update_user(123, '{"firstname": null, "lastname": "doe1"}'::jsonb);
SELECT sp_update_user(123, '{"firstname": "doris"}'::jsonb);
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句