Postgres comparing strings from json and table

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$;

​x
 
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'

 
    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).

Answer

Presumably, you need to extract the email as text, like so:

user_email := req ->> 'email';

 
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.

Advertisement

Answer