Отслеживание функций SQL в Hasura

В основном я пытаюсь сравнить две строки JSONB и вернуть числовое значение. Но я хочу иметь возможность его запросить. Я не уверен, следует ли мне использовать пользовательскую функцию SQL, вычисляемое поле или столбец, созданный Postgres, поэтому мне нужен небольшой совет.

У меня есть столбец jsonb для каждого пользователя, который хранит несколько сотен ключей / значений как таковых:

USERS TABLE:
| username     | user_jsonb_column                          |
|-----------------------------------------------------------|
| 'user1'      |  {"key1":"value1", "key2":"value2" ... }   |
|--------------|--------------------------------------------|
| 'user2'      |  {"key2":"value2", "key3":"value3" ... }   |

Я пытаюсь вычислить сходство строк jsonb двух пользователей с помощью очень простого SQL-запроса как такового:

SELECT ROUND ((

SELECT COUNT(*) from (
    SELECT jsonb_each(user_jsonb_column)
    FROM users WHERE username = 'johndoe'
      INTERSECT
    SELECT jsonb_each(user_jsonb_column)
    FROM users WHERE username = 'janedoe'
)::decimal AS SAME_PAIRS

/ --divide it by

SELECT COUNT(*) from (
    SELECT jsonb_object_keys(user_jsonb_column)
    FROM users WHERE username = 'johndoe'
      INTERSECT
    SELECT jsonb_object_keys(user_jsonb_column)
    FROM users WHERE username = 'janedoe'
  ) as SAME_KEYS

) * 100) as similarity_percentage

Это работает так, как задумано, и дает мне результат сходства между двумя объектами json в процентах.

Я пытаюсь превратить это в функцию, чтобы я мог запросить процент сходства двух пользователей как таковых:

query {
  calculate_similarity_percentage(
    args: {user1: "johndoe", user2: "janedoe"}
  ){
      similarity_percentage_value
  }
}

Но я застрял на этом этапе, потому что не уверен, следует ли мне мыслить в терминах trackable custom SQL function (который должен возвращать SETOF <TABLE>, но мне нужно числовое значение), computed field (который также может возвращать тип BASE) или, может быть, Postgres generated column в моей ситуации.

Я читал https://hasura.io/docs/1.0/graphql/core/schema/custom-functions.html и https://hasura.io/docs/1.0/graphql/core/schema/computed-fields.html, но я не мог понять, как к этому подойти, поэтому любая помощь или комментарий будут оценены.

Обновление: Да, как отметила Лауренс Альбе, я могу создать такую ​​функцию:

CREATE OR REPLACE FUNCTION public.calculate_similarity_percentage(text, text)
 RETURNS numeric
 LANGUAGE sql
 STABLE
AS $function$

SELECT ROUND(
(select count(*) from (
    SELECT jsonb_each(user_jsonb_column) FROM users WHERE username = $1
    INTERSECT
    SELECT jsonb_each(user_jsonb_column) FROM users WHERE username = $2
    ) as SAME_PAIRS
)::decimal / (
    select count(*) from (
    SELECT jsonb_object_keys(user_jsonb_column) FROM users WHERE username = $1
    INTERSECT
    SELECT jsonb_object_keys(user_jsonb_column) FROM users WHERE username = $2
    ) as SAME_KEYS
    )
    * 100) as similarity_percentage

$function$

Тогда я могу выполнить эту функцию:

SELECT calculate_similarity_percentage('johndoe','janedoe')

И он без проблем возвращает это:

similarity_percentage
62

Однако я хотел бы, чтобы Хасура отслеживала эту функцию, чтобы я мог запрашивать ее на graphQL как:

query MyQuery {
  calculate_similarity_percentage(args: {user1: "johndoe", user2: "janedoe"}) {
    similarity_percentage
  }
}

Но если я попытаюсь track выполнить указанную выше функцию, Хасура скажет:

**SQL Execution Failed**
in function "calculate_similarity_percentage":
the function "calculate_similarity_percentage" cannot be tracked for the following reasons:
• the function does not return a "COMPOSITE" type
• the function does not return a SETOF
• the function does not return a SETOF table

Я понятия не имею, смогу ли я найти обходной путь и вернуть числовое значение в виде таблицы COMPOSITE или SETOF.


postgresql hasura
person Furkan    schedule 21.10.2020    source источник
comment
@LaurenzAlbe: Извините, я не совсем понял, я добавил в свой вопрос дополнительную часть. Проблема в том, что я не могу заставить Хасуру отслеживать функцию, чтобы она отображалась на graphQL.   -  person Furkan    schedule 21.10.2020

Ответы (1)


arrow_upward
0
arrow_downward

Вот как я решил свое дело. Но это не было оптимальным решением, поэтому я не принимаю это как ответ.

В итоге я создал еще одну такую ​​таблицу:


USER_RELATION_TABLE:
| user1_col   | user2_col  |
|--------------------------|
| 'johndoe'   | 'janedoe'  |
|--------------------------|
| 'brad'      | 'angelina' |
|--------------------------|
|     ...     |     ...    |

Затем я добавил вычисляемое поле в таблицу отношений со следующей функцией:

CREATE OR REPLACE FUNCTION public.calculate_similarity_percentage(user_relation_row user_relation_table)
 RETURNS numeric
 LANGUAGE sql
 STABLE
AS $function$

SELECT ROUND(
(select count(*) from (
    SELECT jsonb_each(user_jsonb_column) FROM users
    WHERE username = user_relation_row.user1_col
    INTERSECT
    SELECT jsonb_each(user_jsonb_column) FROM users
    WHERE username = user_relation_row.user2_col
    ) as SAME_PAIRS
)::decimal / (
    select count(*) from (
    SELECT jsonb_object_keys(user_jsonb_column) FROM users
    WHERE username = user_relation_row.user1_col
    INTERSECT
    SELECT jsonb_object_keys(user_jsonb_column) FROM users
    WHERE username = user_relation_row.user2_col
    ) as SAME_KEYS
    )
    * 100) as similarity_percentage

$function$

Теперь я могу запросить его на graphQL следующим образом:

query MyQuery {
  user_relation_table {
    similarity
  }
}
person Furkan    schedule 23.10.2020