Allow using database functions as GraphQL selectors instead of just tables and views.

[dosco]

Every function can return a table, ref https://www.postgresql.org/docs/current/sql-createfunction.html

EDIT:

Some of our functions also return a boolean, or a set or other return types, as mentioned in https://www.postgresql.org/docs/current/xfunc-sql.html

Originally posted by @krish7919 in #312 (comment)

[krish7919]

Hi @dosco, just checking in to see if there is a roadmap for this?

[dosco]

Yes plan to work on it next week. working on another bug fix at the moment.

[dosco]

Two new tests added to postgres_test.go to serve as examples to how this new functionality can be used. It will only work with functions that return records (aka tables). You can pass arguments to the functions with the args parameter. I can implement the argument passing as an object with name-value pairs but this was quicker for now.

CREATE OR REPLACE FUNCTION get_latest_users (n INTEGER, tag TEXT) 
RETURNS TABLE ( tag_name TEXT, id BIGINT, full_name TEXT ) 
LANGUAGE plpgsql 
as $$
BEGIN
	RETURN QUERY select tag as tag_name, u.id, u.full_name from users u order by u.id desc limit n;
END; $$; 

query {
  get_latest_users(limit: 2, args: [4, $tag]) {
	  tag_name
	  id
	  full_name
  }
}

[krish7919]

A SQL function in Postgres can return this:

rettype

    The return data type (optionally schema-qualified). The return type can be a base, composite, or domain type, or can reference the type of a table column. Depending on the implementation language it might also be allowed to specify “pseudo-types” such as cstring. If the function is not supposed to return a value, specify void as the return type.

    When there are OUT or INOUT parameters, the RETURNS clause can be omitted. If present, it must agree with the result type implied by the output parameters: RECORD if there are multiple output parameters, or the same type as the single output parameter.

    The SETOF modifier indicates that the function will return a set of items, rather than a single item.

    The type of a column is referenced by writing table_name.column_name%TYPE.

Reference https://www.postgresql.org/docs/current/sql-createfunction.html

postgraphile supports all of this instead of just a record.

EDIT:

For our specific use case, we return a custom type in Postgres created using CREATE TYPE.

[dosco]

Can you provide an example of a query using a function that returns non record values for example one that returns a string or boolean. How do you use a function as a selector in your query? An example will be helpful.

In GraphJin selectors are equal to tables they can have limit, where and other arguments. Open to evolving this I just need some examples beyond the link to the postgres doc on functions.

[dosco]

For our specific use case, we return a custom type in Postgres created using CREATE TYPE.

Examples really help can you give me an example of this type of function (does it not return a type inherited from record) also an example GraphQL query using this will help.

[dosco]

FYI Working on a fix

[dosco]

Added support for functions with user-defined types CREATE TYPE. Next up working on adding support for functions that return singular values.

[dosco]

does this work for you? please close this issue if it does.