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. — a_horse_with_no_name
@a_horse_with_no_name Done. But I'm not sure that's very useful to answer this question... — ManUtopiK
Computed column is on the table photo. I did everything directly in the Hasura console, not with SQL. — ManUtopiK
There is no computed column in the create table statement for the photo table — a_horse_with_no_name

Leonardo Alves Leonardo Alves · Accepted Answer · 2020-05-13T14:46:44

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$

Using Hasura session in PostgreSQL function for computed field

1 Answers