PostgreSQL Functions in Supabase

---

I love the RESTful API generated automatically by PostgREST from my Supabase PostgreSQL schema. However, at the beginning I did have concerns about how I could handle more complex queries … that was at least until I read more about PostgreSQL Functions and how to build them in Supabase!

This is the Netlify deploy data dashboard I was working towards building and I wanted to avoid introducing a calendar datepicker interface but rather only show the the years and months I had build data for from my deploys. Below is a table representing my database columns with some sample data for a row

Netlify deploy data table

id	1
deploy_id	xxxxxxxxxx
branch	main
context	production
deploy_time	38
created_at	2021-07-22T18:11:31.959+00:00

What I wanted was to query the database and return a column of unique years present in the created_at column. Additionally, for each year I wanted another column consisting of an array of unique months and I could use this to build out the buttons on the dashboard interface. Essentially the result on my dummy data would look similar to the following

Unique years and months

years	month_numbers
2020	{9,10,11,12}
2021	{1,2,3,4,5,6,7}

I could write the following SQL query to generate this result

SELECT
  distinct(EXTRACT(YEAR FROM created_at)) AS year,
  array_agg(distinct(EXTRACT(MONTH FROM created_at))) AS month_numbers
FROM public.netlify_deploy_data
GROUP BY year
ORDER BY year ASC

but the problem was using the JavaScript Supabase client to create the query. I'm not sure this can be done through the client but that was when I read about PostgreSQL Functions in Supabase and saw how it could simplify things for me.

PostgreSQL Functions

I think this is a fairly new addition to Supabase but I found it really straight forward to use. In your Supabase dashboard for your organisation, navigate to /database/functions where you can create a new function.

First up, give your function a name (netlify_deploy_data_years_and_months in my case) and next select a return type. Now, this is where I encountered another brief stumbling block because I could only return a single record or a value of a certain type from what I could see. However, I did see the option for returning JSON and I figured if I could wrap my SQL query to return JSON then I could use that no matter how many records I had. After playing with the queries locally I managed to get things working with the following

SELECT array_to_json(array_agg(row_to_json(t)))
FROM (
  SELECT
    distinct(EXTRACT(YEAR FROM created_at)) AS year,
    array_agg(distinct(EXTRACT(MONTH FROM created_at))) AS month_numbers
  FROM public.netlify_deploy_data
  GROUP BY year
  ORDER BY year ASC
)t;

which gives me back

[{"year":2020,"month_numbers":[9,10,11,12]}, {"year":2021,"month_numbers":[1,2,3,4,5,6,7]}]

and now I could use the JSON return type for my function.

In order to add this SQL to the function rather than using plpgsql, I had to click the toggle for show advanced settings and change the language to sql. Now you can add the SQL snippet from above to the Definition section.

This now makes it really easy to query using the JavaScript client with the following

const { data, error } = await supabase
  .rpc("netlify_deploy_data_years_and_months")

As I use Supabase more, I'm finding more functionality and I have to say that I'm really impressed with what I've seen, it's a fantastic product.

---

---