docs: fix confusion about functions return types

[marionschleifer]

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]

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]

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

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

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]

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

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]

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]

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]

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!

[happyhunter7]

@ooxaam Same issue 2022 ;((( @vishwasnavadak