Skip to content

Instantly share code, notes, and snippets.

@ianks

ianks/slugify.sql

Last active Apr 22, 2021
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

This comment has been minimized.

Copy link

@garlou garlou commented Mar 30, 2020

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

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