Add a trigger in postgres to make a slug

A post in Affill.io, the SaaS I’m making, can have a customizable slug as its URL. But only if the post is made from a subscribed account, if its a free account the slug will be the posts id.

If I just make a new row for the free accounts post I would have to make the slug column empty since I dont know what the next id would be.

So how can I use the database id as the slug in the new database row if I dont know what the id will be?

The answer I found for this is database triggers. I use supabase as the database host for this project, and I can either enter plain SQL to make the trigger and the function, or I can use their UI in the admin part of supabase.

The PL SQL code for the function to get the id of the new post would be something like this:

CREATE FUNCTION create_post_id_as_slug() RETURNS trigger AS $$
BEGIN
    IF NEW.paid is FALSE THEN
    NEW.slug := NEW.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Here I have first added a column called “paid”, and I do a check in javascript before inserting the row to the database. If the user has a subscription I set the paid property of the javascript object to true, if its a free account it gets set to false.

“NEW” in the function is the data for the row we are inserting, so it contains all the columns you defined in the insert query.

We check if the “paid” is false, and if so - we set the “slug” to be equal to the “id” that the postgres database has now generated for this new row.

Now we have to make the trigger that makes postgres run this function. The trigger is simple and looks like this:

CREATE TRIGGER post_slug_trigger
BEFORE INSERT ON "Post"
FOR EACH ROW
EXECUTE PROCEDURE create_post_id_as_slug()

Triggers can be set to run either BEFORE or AFTER the action.
And actions can be insert, update, delete on the row.
And triggers can run for each row, or after the whole statement. Here we want to run it per row. This means that if we do 10 inserts, the trigger is run for every insert - total 10 times.

If its set to run after the statement and we say update 10 rows with one query, the trigger would then run only once.

So now I can get these kind of urls for paid users: https://affill.io/skorpio/lego-collection

And free users gets the post id as its url/slug like this: https://affill.io/example/1003

In the supabase UI you can find functions and triggers under the “Database” menu choice.