Graphql-engine: docs: fix confusion about functions return types

Created on 30 Apr 2020 · 6Comments · Source: hasura/graphql-engine

by @abooij

Return type: MUST be SETOF <table-name>
https://hasura.io/docs/1.0/graphql/manual/schema/custom-functions.html#supported-sql-functions

Computed fields whose associated SQL function returns a base type like Integer, Boolean, Geography etc. are scalar computed fields.
https://hasura.io/docs/1.0/graphql/manual/schema/computed-fields.html#computed-field-types

There is a seeming inconsistency in the docs here: are supported SQL functions allowed to have non-setof return types? The former says it's not, the latter says it is. The answer is that SQL functions can be used in three different places:

As a "custom function", which adds it as an alternative to the standard query, and the return type must be setof <table>:

    query q {
      author(..) {
        first_name
        last_name
        full_name
      }
      search_articles(args: {..}) {}
    }

As a "table computed field", which adds it as a field as part of the object, and the return type must be setof , where the result is an object:

    query {
      author {
        id
        first_name
        last_name
        filtered_articles(args: {search: "Hasura"}){
          id
          title
          content
        }
      }
    }

As a "scalar computed field", which adds it as a field as part of the object, and the return type must be a scalar, and the result is simply an additional field in the object:

    query {
      author {
        id
        first_name
        last_name
        full_name
      }
    }

I think it may be valuable to make a few cross-references in the documentation emphasising the distinctions between these three. Especially since SQL functions are both supported on their own, and as part of the computed fields feature.

docs postgres

Source

marionschleifer

👍2

Most helpful comment

i don't think the scalar computed field is working. i keep getting

the function does not return a "COMPOSITE" type • the function does not return a SETOF • the function does not return a SETOF table

ndao on 11 May 2020

👍2

All 6 comments

i don't think the scalar computed field is working. i keep getting

the function does not return a "COMPOSITE" type • the function does not return a SETOF • the function does not return a SETOF table

ndao on 11 May 2020

👍2

@ndao: Could you please open a new issue for this? Or join us on Discord. Please include the details of the SQL function you are trying to add as a scalar computed field, and details of your existing Postgres tables.

abooij on 11 May 2020

@ndao had the same problem right now. Wanted to create simple time_diff function and I don't know what to do now =)

CREATE FUNCTION time_diff(timestamp, timestamp) RETURNS integer
AS 'select EXTRACT(EPOCH FROM $1::timestamp)::integer - EXTRACT(EPOCH FROM $2::timestamp)::integer;'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

mrspartak on 31 May 2020

I even tried an example from the docs. Doesn't work either

CREATE FUNCTION time_diff(timer_row timer) RETURNS Integer AS $$
SELECT EXTRACT(EPOCH FROM timer_row.time_finish)::Integer - EXTRACT(EPOCH FROM timer_row.time_start)::Integer
$$ LANGUAGE sql STABLE;

mrspartak on 31 May 2020

👍1

@ndao @mrspartak
As Gavin Ray (apologies, I don't know his GitHub) pointed out on discord, it seems like an issue but there is a quick work around for the people who are facing it right now.

just create it un-tracked (maybe test the SQL a few times first to make sure it's working correctly to avoid headaches), and then create the computed field through Hasura Schema API as given below

POST http://localhost:8080/v1/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin
{
    "type":"add_computed_field",
    "args":{
        "table":{
            "name":"language",
            "schema":"public"
        },
        "name":<your column name here>,
        "definition":{
            "function":{
                "name":"generate_lang_code",
                "schema":"public"
            },
            "table_argument":"lang_row"
        }
    }
}

vishwasnavadak on 3 Jun 2020

👍1

Is there any update on this? I'm seeing the same issue running

CREATE OR REPLACE FUNCTION user_is_registered_for_game(user_row users)
RETURNS boolean AS $$
SELECT EXISTS (
    SELECT game_sets.* FROM game_sets
    JOIN games ON game_sets.game_id=games.id
    JOIN users ON game_sets.set_by_id=users.id
    WHERE users.id = user_row.id
    AND (games.status = 'POSTPONED' OR games.status = 'UPCOMING')
);
$$ LANGUAGE sql STABLE;