Skip to content

Instantly share code, notes, and snippets.

@ianks
Last active Jul 28, 2022
Embed
What would you like to do?
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();
@garlou
Copy link

garlou commented Mar 30, 2020

Thanks for this! Its missing a ; at the final of create extension command.

@Zaffer
Copy link

Zaffer commented Jul 18, 2021

@abn

`-- original source: https://medium.com/adhawk-engineering/using-postgresql-to-generate-slugs-5ec9dd759e88

-- https://www.postgresql.org/docs/9.6/unaccent.html
CREATE EXTENSION IF NOT EXISTS unaccent;

-- create the function in the public schema
CREATE OR REPLACE FUNCTION public.slugify(
v TEXT
) RETURNS TEXT
LANGUAGE plpgsql
STRICT IMMUTABLE AS
$function$
BEGIN
-- 1. trim trailing and leading whitespaces from text
-- 2. remove accents (diacritic signs) from a given text
-- 3. lowercase unaccented text
-- 4. replace non-alphanumeric (excluding hyphen, underscore) with a hyphen
-- 5. trim leading and trailing hyphens
RETURN trim(BOTH '-' FROM regexp_replace(lower(unaccent(trim(v))), '[^a-z0-9\\-_]+', '-', 'gi'));
END;
$function$;`

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment