Using Hasura session in PostgreSQL function for computed field

Question

I created a "favorite" functionality, which is similar to the common "Like" functionality in many websites.

There are 3 tables:

"User" with primary key UUID
"Photo" with pk UUID
"Favorite" with pk user.UUID and post.UUID

The corresponding SQL is:

CREATE TABLE public."user" (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL
);
CREATE TABLE public."photo" (
    id uuid DEFAULT public.gen_random_uuid() NOT NULL
);
CREATE TABLE public."favorite" (
    userId uuid NOT NULL
    photoId uuid NOT NULL
);

Now, I would like to query photos with a computed field isFavorite as boolean where the value is set to true when the current user has favorited the photo.

So, I created this custom SQL function:

CREATE OR REPLACE FUNCTION public.isfavorite(photo photo, hasura_session json)
 RETURNS boolean
 LANGUAGE sql
 STABLE
AS $function$
SELECT EXISTS (
    SELECT *
    FROM public.favorite
    WHERE "userId" = (VALUES (hasura_session ->> 'x-hasura-role'))::uuid AND "photoId" = photo.uuid
)
$function$

~~I can create this function with SQL in Hasura, but when I set this function to a computed field in the photo table, Hasura display this error:~~

in table "photo": in computed field "isFavorite": function "isfavorite" is overloaded. Overloaded functions are not supported

Where I made a mistake? Can we build a custom function that return boolean? How do you build a favorite (or like) functionality?

Solved: There was two isFavorite functions in the database that cause overloading...

So now there is a isFavorite field in the photo schema, but I need te provide $args with hasura_session as argument. How to provide hasura_session without the need to fill in arguments?

Please **[edit]** your question and add the `CREATE TABLE` statement for the table in question. — , May 11 '20 at 15:32
@a_horse_with_no_name Done. But I'm not sure that's very useful to answer this question... — ManUtopiK, May 11 '20 at 15:44
Computed column is on the table photo. I did everything directly in the Hasura console, not with SQL. — ManUtopiK, May 11 '20 at 16:25
There is no computed column in the create table statement for the photo table — , May 11 '20 at 17:07
Yes for sure. This is because I don't know what is my create table statement. I create this table and computed column directly from the Hasura console. — ManUtopiK, May 11 '20 at 20:52

Leonardo Alves · Accepted Answer · 2020-06-30T14:23:48.943

2

You will need to track your computed column passing the session variable.
https://hasura.io/docs/1.0/graphql/manual/api-reference/schema-metadata-api/computed-field.html

{
    "type":"add_computed_field",
    "args":{
        "table":{
            "name":"photo",
            "schema":"public"
        },
        "name":"isfavorite",
        "definition":{
            "function":{
                "name":"isfavorite",
                "schema":"public"
            },
            "table_argument":"photo_row",
            "session_argument": "hasura_session"
        }
    }
}

This was also added recently. Make sure your are on version v1.3 or later. I would also change the function to accept photo_row as the variable, instead of photo photo this might cause issues with PostgreSQL.

CREATE OR REPLACE FUNCTION public.isfavorite(photo_row photo, hasura_session json)
 RETURNS boolean
 LANGUAGE sql
 STABLE
AS $function$
SELECT EXISTS (
    SELECT *
    FROM public.favorite
    WHERE "userId" = (VALUES (hasura_session ->> 'x-hasura-role'))::uuid AND "photoId" = photo.uuid
)
$function$

edited Jun 30 '20 at 14:23

answered May 13 '20 at 14:46

Leonardo Alves

1,876
1
16
19

Thx for your answer! This is exactly what I did after reading this https://github.com/hasura/graphql-engine/blob/9a16e257695b4eb5defca8c3a7784f1ac7a35078/CHANGELOG.md Response to `v1/query` of my Hasura 1.2.1 endpoint is success. But I still need to provide args. If I export all metadadas, I can see session_argument is not set. Is there a bug? – ManUtopiK May 13 '20 at 21:42
Ok, this feature is not yet released: https://github.com/hasura/graphql-engine/issues/4594#issuecomment-621235996 – ManUtopiK May 13 '20 at 21:56
2

I saw that this feature was merged 25 days ago. Since it was also in the docs I thought it was already released. I didn't actually try it. – Leonardo Alves May 14 '20 at 00:58

Using Hasura session in PostgreSQL function for computed field

1 Answers1