I have the following postgres stored function:
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 user_email := req->'email'; raise notice '(%)', user_email; 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$;
I call this with:
select wg.get_user( ‘{ “email”: “x@x” }’::jsonb);
and I have a row with ‘x@x’ in the email column, defined as VARCHAR(100). The following SQL returns my row:
SELECT email, name, default_project_id FROM wg.users WHERE email = 'x@x'
The notice call within the stored function returns:
NOTICE: (“x@x”)
However, The function is returning NULL because apparently the WHERE clause does not match, even though both seem to evaluate as “x@x”.
It works if I change the comparison to inequality (email > user_email works).
Advertisement
Answer
Presumably, you need to extract the email as text, like so:
user_email := req ->> 'email';
->
extracts a json string, so for your sample data this yields: "x@x"
.
On the other hand, ->>
extracts the data as text, which produce x@x
.