Generating Slugs in Postgres
CREATE EXTENSION IF NOT EXISTS "unaccent"; | |
CREATE OR REPLACE FUNCTION slugify("value" TEXT) | |
RETURNS TEXT AS $$ | |
-- removes accents (diacritic signs) from a given string -- | |
WITH "unaccented" AS ( | |
SELECT unaccent("value") AS "value" | |
), | |
-- lowercases the string | |
"lowercase" AS ( | |
SELECT lower("value") AS "value" | |
FROM "unaccented" | |
), | |
-- replaces anything that's not a letter, number, hyphen('-'), or underscore('_') with a hyphen('-') | |
"hyphenated" AS ( | |
SELECT regexp_replace("value", '[^a-z0-9\\-_]+', '-', 'gi') AS "value" | |
FROM "lowercase" | |
), | |
-- trims hyphens('-') if they exist on the head or tail of the string | |
"trimmed" AS ( | |
SELECT regexp_replace(regexp_replace("value", '\\-+$', ''), '^\\-', '') AS "value" | |
FROM "hyphenated" | |
) | |
SELECT "value" FROM "trimmed"; | |
$$ LANGUAGE SQL STRICT IMMUTABLE; |
CREATE FUNCTION public.set_slug_from_name() RETURNS trigger | |
LANGUAGE plpgsql | |
AS $$ | |
BEGIN | |
NEW.slug := slugify(NEW.name); | |
RETURN NEW; | |
END | |
$$; | |
CREATE TRIGGER "trg_slug_insert" | |
BEFORE INSERT ON "my_table_name" | |
FOR EACH ROW | |
WHEN (NEW.name IS NOT NULL AND NEW.slug IS NULL) | |
EXECUTE PROCEDURE set_slug_from_name(); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Thanks for this! Its missing a ; at the final of create extension command.