Graphql-engine: expose postgres functions as queries and mutations

[0x777]

This is kind of an escape hatch for some of the features that we do not support out of the box.

[revskill10]

This is a very important feature, with it we could incrementally adapt it for more stable solutions in future.

[shahidhk]

[via 5b5e3ac6f396c9d6783af009 on intercom]

[sfcgeorge]

It would be neat to use a custom function as a way of wrapping ZomboDB queries — effectively letting you do ElasticSearch queries on your Postgres database over GraphQL. The tricky bit I see to get right would be access control on the function's parameters.

[coco98]

@sfcgeorge This is on the roadmap and work has just started. Should be out in a 1-2 weeks!

Like you said, access control is the tricky bit. The access control around functions is that you can "set" some parameters of the function to come in directly from the auth/session headers.

The super cool thing is that you can have relationships on the functions output type. So you can have other tables have relationships to this function and vice-versa. I can't wait for this to be out!

[deinspanjer]

The part I'm most interested in here are ways to support new WHERE clause criteria for currently unsupported or custom data types. A good example is the ltree data type which is very powerful and useful when dealing with tree like data. Using that, you have a new set of operators that let you compare an ltree column with an lquery. Most of those operators look and behave similar to the JSON(B) operators.

[dsandip]

(not mocking it up as it's pretty much the same as the UI for handling tables)
We'll be treating functions the same way as Views as much as possible:
1) Show the tracked functions after tables/views. Show list of untracked supported functions (who's SETOF tables have already been tracked). If no such functions exist, then don't display this section at all:

2) A similar interface to views for modifying/displaying the definition of views:

3) Clicking on modify prefills the Raw SQL page. There are a couple of copy changes to this page (and not just for this context):

@coco98 @0x777 @rikinsk @shahidhk @karthikvt26 Modifed as discussed

[haasel77]

@dsandip I can see this issue being re-assigned many times between consecutive milestones. Let me kindly ask what are the chances that this and #1073 are closed soon within the v1.0.0-alpha34 so that the postgres functions support is enabled?

[coco98]

@haasel77 This is happening in the next release and should be out next week 🎉
Most of the backend work is done and we're just adding stuff to the console and docs. Sorry for having kept you waiting! 😞 🙏

@dsandip Let's add these 2 use-cases to our docs when this goes out:
1) PostGIS queries
2) Fulltext search queries

If you all have other use-cases that you would like to see docs for please let us know!

[dsandip]

@rakeshkky Please to include 👆 in your outline and docs content.

[phated]

It seems like the implementation in #1073 won't support mutations because the functions must be STABLE or IMMUTABLE, is this correct?