docs: fix confusion about functions return types

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.

ndao commented 4 years ago

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

abooij commented 4 years ago

@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.

mrspartak commented 4 years ago

@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 commented 4 years ago

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;

vishwasnavadak commented 4 years ago

@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"
        }
    }
}

jrnxf commented 3 years ago

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;

rossng commented 3 years ago

I'm having the same problem when trying to add a function that adds an interval to a timestamp.

CREATE OR REPLACE FUNCTION public.event_end_time(event_row "Event")
  RETURNS timestamptz
  LANGUAGE sql
  STABLE
AS $function$
  SELECT event_row."startTime" + (event_row."durationSeconds" * interval '1 second') LIMIT 1
$function$;

PopBot commented 2 years ago

Is there a fix yet?

Is still not working for me as of Feb 26, 2022.

CREATE OR REPLACE function online_presence(user_id text) returns boolean
    stable
    language plpgsql
    as $$
    DECLARE
        online_devices int;
    BEGIN
        SELECT COUNT(*) INTO online_devices FROM device WHERE user = user_id AND is_online = true;
        RETURN online_devices > 0;
    END;
$$;

oalexdoda commented 2 years ago

The computed fields function in the doc doesn't work either:

CREATE FUNCTION author_full_name(author_row authors)
RETURNS TEXT AS $$
  SELECT author_row.first_name || ' ' || author_row.last_name
$$ LANGUAGE sql STABLE;

https://hasura.io/docs/latest/graphql/core/databases/postgres/schema/computed-fields/

Throws: ...cannot be tracked for the following reasons: • the function does not return a "COMPOSITE" type • the function does not return a table...

Any thoughts on this? Thanks!

gherciu commented 2 years ago

@ooxaam Same issue 2022 ;((( @vishwasnavadak

aaron-manning commented 1 year ago

Still broken. example from the docs complains about not being a composite type.

andynguyen11 commented 1 year ago

I'm having this issue as well. Followed instructions and samples from computed scalar fields doc here: https://hasura.io/docs/latest/schema/postgres/computed-fields/#1-scalar-computed-fields

I'm getting:

...cannot be tracked for the following reasons: • the function does not return a "COMPOSITE" type • the function does not return a table...

Is this supported or is this not supported? Somewhat frustrating that this issue is 3 years old without any resolution when official docs says otherwise. Docs say 2.x and we are running 2.13.2.

SamirTalwar commented 1 year ago

It looks like the issue is that you're trying to track the function, and Hasura is assuming that it's of a supported function type, not a computed field type.

Instead of running the SQL in the "SQL" pane, have you tried following the instructions in the computed fields documentation? You can add the function definition as part of adding the computed field, and there, Hasura will know to track it as such.

andynguyen11 commented 1 year ago

It looks like the issue is that you're trying to track the function, and Hasura is assuming that it's of a supported function type, not a computed field type.

Instead of running the SQL in the "SQL" pane, have you tried following the instructions in the computed fields documentation? You can add the function definition as part of adding the computed field, and there, Hasura will know to track it as such.

Looks like not tracking it did the trick. It wasn't super clear in the docs to explicitly not track in combo with my misunderstanding of what tracking means. In my head, I needed to track it so that it appears in the function drop down for computed fields. I'm also on an older version so the only option was to "Create New" function which opens the sql pane. Not sure if there is an inline function add now. Maybe a sign to upgrade.

Thanks for the help! I was spinning my wheels on this for awhile.

hasura / graphql-engine

docs: fix confusion about functions return types #4629