Postgres comparing strings from json and table

Question

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

GMB · Accepted Answer · 2020-12-09 21:26:35Z

4

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.

answered Dec 9, 2020 at 21:26

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Miner_Glitch Over a year ago

Thanks, that was it. I hadn't noticed that the JSON strings still had quotes around them!

Collectives™ on Stack Overflow

Postgres comparing strings from json and table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related