Using Custom SQL functions for queries with Postgres and GraphQL

Directly query custom PostgreSQL functions in the GraphQL API

TL;DR

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

Feature overview

Currently, only the following types of Postgres functions can be queried in the GraphQL API (terminology from Postgres docs):

• Function behavior : ONLY STABLE or IMMUTABLE
• Return type : MUST be SETOF <table-name>
• Argument modes : ONLY IN

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 Data -> 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):

query custom function, search_articles, directly in GraphQL API

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 Data -> 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:

query:

example response:

Please note that you can also:

• Use aggregations on custom functions using the using the <function-name>_aggregate field.
• Use arguments like where, limit, order_by, 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!