Using Custom SQL functions for queries with Postgres and GraphQL
Directly query custom PostgreSQL functions in the GraphQL API
You can now run queries (or subscriptions) such as the following:
search_articles is a custom SQL function that takes a text input to search an
article table (detailed example below)
Custom SQL functions
Custom SQL functions are procedures that you can define which are stored by in database and can be invoked to run on the database and return the final result.
This can be used to run some common business logic directly on the database itself which might otherwise:
- have to be replicated on all of the database’s clients
- require multiple queries to the database to compute
Currently, only the following types of Postgres functions can be queried in the GraphQL API (terminology from Postgres docs):
- Function behavior: ONLY
- Return type: MUST be
- Argument modes: ONLY
Note: You can also support functions that need to return a custom type i.e. row set by creating and tracking an empty table with the required schema to support the function- see the 2nd example below.
Access control for custom functions
Access control permissions configured for the
SETOF table of a function are also applicable to the function itself.
How to query functions in GraphQL API
Let’s see you can use this feature through two examples:
Example: A custom text-search function
In the usual context of an article/author schema, let’s say we’ve created and tracked a custom function,
search_articles with the following definition:
(This is just an example function, you could have used the
ilike argument to filter results from the article
table too ? )
This function filters rows from the
article table based on the input text argument,
search i.e. it returns
SETOF article. Once you’ve created and tracked this function (using the
SQL section of the Hasura console), you can call the custom function in the GraphQL API (using the example query in the TL;DR section):
Example: A PostGIS-based function
There may be instances when your custom function returns a result-set or rows of a table that is not part of your existing schema. In this scenario, you are required to create and track this table to add support for your function in the GraphQL API. Let’s see how we can do that using a PostGIS based example.
Say you have 2 tables, for user and landmark location data, with the following definitions:
You can populate these tables with test data using the following mutations and variables:
In this example, we want to fetch a list of landmarks that are near a given user, along with the user’s details in the same query. PostGIS’ built-in function,
ST_Distance can be used to implement this use case.
Since our use-case requires an output that isn’t a “subset” of any of the existing tables i.e. the
SETOF table doesn’t exist, let’s first create this table and then create our location search function using the following definitions (once again, we’ll use the
SQL section of the Hasura console):
This function fetches user information (for the given input
userid) and a list of landmarks which are less than
distance_kms kilometers away from the user’s location as a JSON field. We can now refer to this function in our GraphQL API and get the corresponding response as shown below:
Please note that you can also:
- Use aggregations on custom functions using the using the
- Use arguments like
offset, etc. with function-based queries.
Try it out
Do take this feature for a spin and let us know what you think! Support for more functions types and for functions in mutations is on its way. Give us a shoutout on on the community Discord or Github if you want to be notified of updates on the iterations to follow!