Using Custom SQL functions for queries with Postgres and GraphQL
Tags: tutorial, postgres, database, graphql
Published: 2019-12-24 06:53:38

---
title: Using Custom SQL functions for queries with Postgres and GraphQL
published: true
date: 2018-08-16 00:00:00 UTC
tags: tutorial, postgres, database, graphql
canonical_url: https://blog.hasura.io/using-custom-sql-functions-for-queries-with-postgres-and-graphql-ddca6caf6681/
---

 **Directly query custom PostgreSQL functions in the GraphQL API**

![](https://blog.hasura.io/content/images/downloaded_images/using-custom-sql-functions-for-queries-with-postgres-and-graphql-ddca6caf6681/1-Le_7ag5x2iCzW92YgOfQ3g.png)

## TL;DR 

You can now run queries (_or subscriptions_) such as the following:

{% gist https://gist.github.com/dsandip/99ca91391cf95927197432b9194bca7e.js %}

`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](https://www.postgresql.org/docs/current/sql-createfunction.html)):

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

{% gist https://gist.github.com/dsandip/c80dbccf7c1bcc00384c9d6170e13919.js %}

(_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_):

![](https://blog.hasura.io/content/images/2019/01/custom-functions-sample-query-and-response.png)<figcaption>query custom function, <code>search_articles</code>, directly in GraphQL API</figcaption>

### 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](https://postgis.net/) based example.

Say you have 2 tables, for user and landmark location data, with the following definitions:

{% gist https://gist.github.com/dsandip/885f6fac9f86667d7b81e9193817189c.js %}

You can populate these tables with test data using the following mutations and variables:

{% gist https://gist.github.com/dsandip/deae2a7c3d8f7407dcd27c6cd4746281.js %}

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

{% gist https://gist.github.com/dsandip/8f85439eb59adc9c659bafe9d0106c00.js %}

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

{% gist https://gist.github.com/dsandip/c93904d6e6908fb6a84ccacf1e10e782.js %}

**example response:**

{% gist https://gist.github.com/dsandip/e0aef9a2911bae62127b59ead3c1a83f.js %}

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](https://discord.gg/vBPpJkS) or [Github](https://github.com/hasura/graphql-engine) if you want to be notified of updates on the iterations to follow!