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.
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 uuid
s, 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)