https://supabase.com/ logo
Join Discord
Powered by
Is there a way to have a `slug` column that auto s...
# help
r
Is there a way to have a 
slug
column that auto slugifies the value of another column on 
INSERT
and/or 
UPDATE
? I attempted to add this function, but am unable to set the return type to `trigger`: https://www.kdobson.net/2019/ultimate-postgresql-slug-function/ Another option I saw in the comments of the gist (linked at bottom of that blog post) was to have a generated column a la:
Copy code
CREATE TABLE person (
  name  text not null,
  slug  text generated always as (slugify(name)) stored
);
Not sure how to create a generated column within Supabase, though. Any ideas?
s
The article you linked gives an example of how to use it as part of a trigger. Were there any errors thrown when you tried that (and if so, what were the errors)?
r
Wow! 🤦‍♂️ Totally skipped over that, I'll give it a go and report back, thanks!
j
Creating a room, and clicking on it will take you to the slug link https://chatatat.netlify.app/
Definitely a few ways to do it
lmk if the kdobson one strips emojis or not
r
Alright, I'm pretty new to Supabase and Postgres/SQL, so bear with me: I'm unable to save the 
slugify
function with a return type of 
text
(which I'm assuming I need when using with the associated function that returns a 
trigger
). I get 
"Failed to create function: return type mismatch in function declared to return text"
. I'm not sure how to define an 
extension
in the Supabase UI as @User has done with 
unaccent
in their implementation, so I'm unable to try their seeming much simpler trigger function. I've also just tried testing a function that returns a 
trigger
that just copies the 
new.title
value into the 
new.slug
column and I'm getting a 
"stack depth limit exceeded"
error, which I think is due to setting my trigger to run on 
UPDATE
as well as 
INSERT
? Going to grab some food, but I'll keep trying 😛
j
Ah, good point. I haven't tried it on update yet
In the supabase UI you can go to Database > Extensions to enable 
unaccent
.
You can also connect to the db with any client psql, postico, pgadmin, etc and run commands like 
CREATE EXTENSION
r
Awesome, thank you!!
j
I should also mention that in order to match what supabase does, the manual command to install 
unaccent
would be 
create extension unaccent with schema extensions;
there are a lot of pitfalls with slugs
r
Haha, yeah, I think I'm learning that as I go. I now have a working slugify function (it works via the 
supabase.rpc()
API at least) but when I ask a trigger function to run it I get 
"regular expression failed: regular expression is too complex"
🤔 And that's with the trigger set to 
INSERT
only, for now..
I ended up creating the* slugify function through Supabase's SQL editor instead of the Supabases's (Alpha Preview) Functions UI. *the function is kdobson's from above. Then, in the SQL editor, I removed any/all 
slug
column(s) and re-created them with the code below that sets the default value of the 
slug
column to 
slugify(title)
. I initially tried doing this in Supabase's UI but was getting 
"cannot use column reference in DEFAULT expression"
errors, which doesn't seem to be an issue when creating the columns with the code below:
Copy code
ALTER TABLE name_of_table DROP IF EXISTS slug;
ALTER TABLE name_of_table
ADD slug text generated always as (slugify(title)) stored;
ALTHOUGH, I get a 
"column "slug" can only be updated to DEFAULT"
error when updating 
title
cells (at least via the UI) AND, it seems to fail when attempting to add a new user. I set up a 
profiles
table with one of Supabase's templates and it gets populated when a new user signs up. I am attempting to slugify the username and I'm getting a
Copy code
500
Internal Server Error
in the browser (not sure how else to test this one)
PreviousNext
Powered by