Skip to content

Instantly share code, notes, and snippets.

@myobie
Forked from kez/slugify.sql
Created June 1, 2019 16:19
Show Gist options
  • Save myobie/59b6b787c95b308ef51efc0404336fdd to your computer and use it in GitHub Desktop.
Save myobie/59b6b787c95b308ef51efc0404336fdd to your computer and use it in GitHub Desktop.
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"
),
-- remove single and double quotes
"removed_quotes" AS (
SELECT regexp_replace("value", '[''"]+', '', 'gi') AS "value"
FROM "lowercase"
),
-- 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 "removed_quotes"
),
-- 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