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

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.

CodePudding user response：

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)