Enhance support for UDFs

[imor]

Scope of the PR to add basic support for UDFs has been cut down in the interest of getting basic support out. This is an issue to keep track of those features which were cut from the scope.

The following items need to be supported to further enhance support for UDFs:

• [ ] Add support for UDFs with unnamed arguments. Such arguments need to have synthetic names like arg0, arg1, etc.
• [ ] Add support for overloaded functions.
• [x] Add support for default arguments.
• [ ] Add support for functions returning void.
• [x] Add support for functions accepting or returning array types.
• [ ] Better support for default arguments in __schema
• [ ] Add support for enum and array of enum arguments and return types.
• [ ] Add support for functions returning composite types.

[olirice]

Add support for default arguments

this is done, right?

Add support for UDFs with unnamed arguments

(imo) this one can be skipped

Add support for overloaded functions.

For those one we can probably require a comment directive @graphql({"name": "foo"}) to disambiguate the names to get them to show up in the API

Better support for default arguments in __schema

Can skip for now. as you mentioned, similar software made that choice

Add support for functions returning void.

Can skip for now. It's a low lift to ask users to return a value e.g. return 1; so we can have a meaningful type in the schema (null is not a valid type so we'd otherwise have to pick one at random)

Add support for functions accepting or returning array types.

+1

[ahoopes16]

Being able to support array arguments would be amazing. I am currently attempting to write a custom query so that I can filter records from my courses table based on a foreign relationship to our authors table. I need to be able to filter by multiple authors at once, so we were attempting to make our custom function with an author_ids uuid[] argument.

If there's anything that could be done to help, please let me know! Thanks so much, these custom functions are going to be incredibly useful!

[imor]

@ahoopes16 support for array types is one of the next items we plan to tackle for pg_graphql. For now you can simulate multiple filter arguments by e.g. using a single argument with comma separated values.

[ahoopes16]

Yep, that's exactly the workaround that we came to as well. Thanks!

[tylerdmace]

@imor @ahoopes16 I don't see any supported string splitting functions available to my UDFs. How are you taking a comma delimited string and splitting that to be used for a "where" clause? Can you show me an example of a simple function that does this?

[ahoopes16]

@tylerdmace Sure thing! So for this example, let's say that we have a courses table with a column called author_id. If you want to support a multi-select filter for courses by authors (as we did), you can write a UDF like this:

create or replace function search_courses(author_ids text default null)
returns setof courses
stable
language plpgsql
as
$$
declare
begin
    return query select c.* from courses c
    where
        search_courses.author_ids is null or
        c.author_id::text = any(string_to_array(search_courses.author_ids, ','));
end
$$;

The piece that you're looking for specifically is the string_to_array(<comma-separated string parameter>, ','). This will return all courses records that have an author_id included in author_ids.

All of this being said, it looks like pg_graphql now supports array arguments in UDFs so this workaround is likely no longer necessary 😅 I hope this helps!

[tylerdmace]

I hope this helps!

This was exactly what I was looking for. I swear I had tried to use string_to_array() in the past and it wasn't recognized as an internal function that I could use and threw an error. But I'll revisit this tonight and see if I can get it working. Thank you!

[ahoopes16]

Strange! It's been working great for us on Postgres 15, although string_to_array() was introduced quite a few versions ago so I don't think version should matter here. Either way, glad I could help and I hope you get it working!

[tylerdmace]

It worked perfectly! Thank you so much, my friend.

[andrew222651]

Thanks for the work so far and I've done a small one-time sponsorship to support further work on this issue.