curl https://sh.rustup.rs -sSf | sh
cargo install cargo-pgx
- Note: Handle the number accordingly.
cargo pgx init --pg14 $(which pg_config)
git clone https://github.com/spa5k/slugify-postgres \
&& cd slugify-postgres \
&& cargo pgx install
Check the included Dockerfile for the installation template.
CREATE EXTENSION IF NOT EXISTS slugify;
select slug('hello world');
-- hello-world
-----------------------------
select slug('Æúű--cool?');
-- aeuu-cool
-----------------------------
select slug('jaja---lol-méméméoo--a');
-- jaja-lol-mememeoo-a
-----------------------------
It even supports unicode characters.
select slug('影師嗎');
-- ying-shi-ma
Note - rand by default adds a 5 character random suffix. and add it to parent string using the separator that is used, else the default separator if none is mentioned.
These slugs are generated by appending a string generated through nanoid with length of 5 as default, you can also change the length.
select slug_rand('Компьютер');
-- komp-iuter-lkai
-----------------------------
select slug_rand('Æúű--cool?');
-- aeuu-cool-3epv
-----------------------------
select slug_sep('hello world', '_');
-----------------------------
-- hello_world
select slug_sep('Of course can be paid','*');
-----------------------------
-- of*course*can*be*paid
select slug_rand_sep('heLLo WorlD','_');
-- hello_world_obc5
-----------------------------
select slug_rand_sep('Of course can be paid','*');
-- of*course*can*be*paid*3ibd
-----------------------------
select slug_rand_c('the 101 dalmatians', 10);
-- the-101-dalmatians-mrjygfcnr
-----------------------------
select slug_rand_sep_c('the 101 dalmatians', '_',10);
-- the_101_dalmatians_5en4hhnrt
-----------------------------
Typically you will only want to create a slug when a record is created. If you update the title of something, you probably want to preserve the slug as it may well be part of your websites’ URL structure. You could update your slug each time, but you’d need an audit table to redirect people from the previous slug(s) to the new ones.
Depending on your schema, you can have a function and trigger for each table that requires a slug, OR if you have consistent column names over tables you can create a generic function that faithfully assumes you’ve got a title and a slug column:
CREATE FUNCTION public.set_slug_from_title() RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.slug := slug(NEW.title);
RETURN NEW;
END
$$;
This function returns a trigger, whilst the slugify function returns text. The NEW keyword above is effectively referencing the table row we are updating.
Note that the above function will happily generate duplicate slugs. You could append an ID, hashid or some other bit of random text to shoot for uniqueness.
And finally, to add this trigger to your table(s)…
CREATE TRIGGER "slugify_the_name" BEFORE INSERT ON "table_name" FOR EACH ROW WHEN (NEW.title IS NOT NULL AND NEW.slug IS NULL)
EXECUTE PROCEDURE set_slug_from_title();
Please check the tests below for more possible usecases.
assert_eq!("hello-world", slug("hello world"));
assert_eq!("hello_world", slug_sep("hello world", "_"));
assert_eq!("helloworld", slug_sep("hello world", ""));
assert_eq!("hello%world", slug_sep("hello world", "%%"));
assert_eq!(slug("alice@bob.com"), "alice-bob-com");
assert_eq!(slug("alice@bob.com"), "alice-bob-com");
assert_eq!(slug("10 amazing secrets"), "10-amazing-secrets");
assert_eq!(slug("the 101 dalmatians"), "the-101-dalmatians");
assert_eq!(
slug_rand("the 101 dalmatians").len(),
"the-101-dalmatians".len() + 5
);
assert_eq!(
slug_rand_c("the 101 dalmatians", 10).len(),
"the-101-dalmatians".len() + 10
);
assert_eq!(
slug_rand_sep_c("the 101 dalmatians", "_", 10).len(),
"the-101-dalmatians".len() + 10
);
assert_eq!(slug("lucky number 7"), "lucky-number-7");
assert_eq!(
slug("1000 reasons you are #1"),
"1000-reasons-you-are-1"
);
assert_eq!(slug_sep("hello world", "."), "hello.world");
assert_eq!(slug_sep("hello world", "_"), "hello_world");
assert_eq!(
slug_rand_sep("hello world-", "_").len(),
"hello_world".len() + 5
);
assert_eq!(slug("影師嗎"), "ying-shi-ma");
assert_eq!(slug("Æúű--cool?"), "aeuu-cool");
assert_eq!(
slug("Nín hǎo. Wǒ shì zhōng guó rén"),
"nin-hao-wo-shi-zhong-guo-ren"
);
assert_eq!(slug("jaja---lol-méméméoo--a"), "jaja-lol-mememeoo-a");
assert_eq!(slug("Компьютер"), "komp-iuter");
assert_eq!(slug("Компьютер"), "komp-iuter");
assert_eq!(slug_sep("hello world", "-"), "hello-world");
assert_eq!(slug_sep("hello world", " "), "hello world");
assert_eq!(
"hello-world".len() + 5,
slug_rand("hello world").len()
);
assert_eq!(
"hello_world".len() + 5,
slug_rand_sep("hello world", "_").len()
);