Postgres Trigger Functions

This is the fifth article in a series on using the Supabase platform instead of writing a backend server. The first several articles will cover some useful PostgreSQL topics. See the first article ("Going Serverless With Supabase") in the series for more background.

This article will cover the why and how of PostgreSQL trigger functions.

Setup for the examples

The second article in the series ("Intro to Postgres RLS") has more details on the database tables, but this is the entity-relationship diagram for the examples in this article:

The other articles in this series have so far covered writing and testing Row-Level Security (RLS) policies for these tables, but they aren't required reading for this article.

Why trigger functions?

The previous articles set up RLS policies for the groups and members tables. Any authenticated user can insert a new group, but only admin members can update or delete a group or member records in that group. And users can insert their own member records, but only at the "approved" level, not "admin".

So how are the admin records created in the first place? That's where trigger functions come into play. They are great for handling logic beyond the typical database CRUD operations

A simple trigger function

Here's a trigger function that will handle adding an admin member record:

create function groups_insert_admin_trigger()
returns trigger as $$
begin
  -- To make recovery easier, skip trigger when using service role
  IF auth.uid() IS NULL THEN
    return NEW;
  END IF;
  -- Insert admin members record
  INSERT INTO members (user_id, group_id, level)
    VALUES (auth.uid(), NEW.id, 'admin');
  RETURN NEW;
end;
$$ language plpgsql security definer;

There is a lot to unpack here. First off, the part between the double dollar signs ($$, which you can change to another delimiter if you want) is the actual function. It's written in plpgsql, which is Postgres' built-in procedural language. (You can install the Javascript extension and write the function in JS, but I haven't personally tried that yet.) The language is rather clunky, but understandable.

The function first checks for a null user and immediately returns. This will handle the case where we are loading data from a backup. The next statement inserts the group admin member record using the current user's ID, the new group's ID, and a level of "admin". It then finally returns the new records, which is a requirement of trigger functions.

The part outside of the function is fairly straightforward except for the security definer part at the very end. That causes the trigger function to execute with the security context of the user that created it, which is typically a database administrator. Be very careful with this statement! For my situation, this is the only way to insert an admin member record. And this function will only be called when a new group is inserted, so the risk is low.

One more statement is needed to create the trigger itself:

CREATE TRIGGER groups_insert_admin_trigger
  AFTER INSERT ON groups
  FOR EACH ROW EXECUTE FUNCTION groups_insert_admin_trigger();

Now every time a group is created, the user who created it will be added as an admin member. Perfect!

Trigger function for altering data

Another situation where trigger functions are useful is to modify data before it is inserted or updated. Let's say we want to automatically create a canonical URL-friendly field for each group based on its name. For example, "My taco group" would have the canonical URL "my-taco-group". First we add the new field to the table and then create a trigger function:

create function groups_create_canonical_trigger()
returns trigger as $$
declare
  temp text;
begin
  temp := lower(NEW.name);
  temp := regexp_replace(temp, ' ', '-', 'g');
  NEW.canonical = regexp_replace(temp, '[^\w-]+', '', 'g');
  return NEW;
end;
$$ language plpgsql;

This function declares a temporary variable and uses that to store the group name. Then it converts the name to lowercase, replaces all the spaces with dashes, and removes any non-word characters. It then assigns that value to the "canonical" field and returns the record.

Note that this function does not need an elevated security context, so we left off the security definer phrase at the end. And to create the actual trigger:

CREATE TRIGGER groups_create_canonical_trigger
  BEFORE INSERT ON groups
  FOR EACH ROW EXECUTE FUNCTION groups_create_canonical_trigger();

This will overwrite any value the user provides in the canonical field.

Conclusion

This articles provided some motivation for trigger functions and provided a couple of examples. Let me know in the comments if you have any questions about trigger functions, or need any clarification.

This series of articles is going to pause here, but I will be back soon to write more articles on using other Supabase services! Follow along to catch those articles, too!