Computed fields error -> operator does not exist: uuid = text",

I am trying to add a computed field like in example in Hasura documentation. This is my sql code

CREATE OR REPLACE FUNCTION post_pinned_by_user(post_row p_posts, hasura_session json)
RETURNS boolean AS $$
SELECT EXISTS (
    SELECT 1
    FROM u_pinned_posts A
    WHERE A.user_id = hasura_session ->> 'x-hasura-user-id' AND A.post_id = post_row.id
);
$$ LANGUAGE sql STABLE;

But I am getting an error like that:

{
    "statement": "CREATE OR REPLACE FUNCTION post_pinned_by_user(post_row p_posts, hasura_session json)\nRETURNS boolean AS $$\nSELECT EXISTS (\n    SELECT 1\n    FROM u_pinned_posts A\n    WHERE A.user_id = hasura_session ->> 'X-Hasura-User-Id' AND A.post_id = post_row.id\n);\n$$ LANGUAGE sql STABLE;",
    "prepared": false,
    "error": {
        "exec_status": "FatalError",
        "hint": "No operator matches the given name and argument types. You might need to add explicit type casts.",
        "message": "operator does not exist: uuid = text",
        "status_code": "42883",
        "description": null
    },
    "arguments": []
}

user_id and post_id fields are uuid.
Thanks in advance.

>Solution :

Like the error message is stating, you are trying to compare two values that are of different type and you therefore have to add a typecast. The ->> operator returns text, not a uuid.

The logic in your function is also inefficient, combining these two issues you would get:

CREATE OR REPLACE FUNCTION post_pinned_by_user(post_row p_posts, hasura_session json)
RETURNS boolean AS $$
    SELECT true
    FROM u_pinned_posts A
    WHERE A.user_id::text = hasura_session ->> 'x-hasura-user-id' 
      AND A.post_id = post_row.id
    LIMIT 1;
$$ LANGUAGE sql STABLE;

If u_pinned_posts.user_id is a primary key or if it is indexed, you are actually better off comparing uuids, in which case you could just pass the JSON value into the function as a uuid:

CREATE OR REPLACE FUNCTION post_pinned_by_user(post_row p_posts, hasura_user_id uuid)
RETURNS boolean AS $$
    SELECT true
    FROM u_pinned_posts A
    WHERE A.user_id = hasura_user_id 
      AND A.post_id = post_row.id
    LIMIT 1;
$$ LANGUAGE sql STABLE;

and then call the function like so:

SELECT post_pinned_by_user(post_row, (hasura_session ->> 'x-hasura-user-id)::uuid)

Leave a Reply