Skip to content

Instantly share code, notes, and snippets.

@AWolf81

AWolf81/slugify.sql

Created February 18, 2024 12:23
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save AWolf81/0e5523182a8e6388b624e654f9c560d2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save AWolf81/0e5523182a8e6388b624e654f9c560d2 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. AWolf81 created this gist Feb 18, 2024.
    46 changes: 46 additions & 0 deletions slugify.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,46 @@
    -- License MIT
    -- Place in your schema & update your table name

    -- First, make sure the pgcrypto & unaccent extension is available
    CREATE EXTENSION IF NOT EXISTS unaccent with schema extensions;
    CREATE EXTENSION IF NOT EXISTS pgcrypto with schema extensions;

    CREATE OR REPLACE FUNCTION public.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"
    )
    -- add a 6 character md5 to the slug e.g. -2c4377
    SELECT concat("value", '-', substring(md5(random()::text), 1, 6)) FROM "trimmed";
    $$ LANGUAGE SQL STRICT IMMUTABLE;

    CREATE FUNCTION public.set_slug_from_title() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
    BEGIN
    NEW.slug := public.slugify(NEW.title);
    RETURN NEW;
    END
    $$;

    CREATE TRIGGER "trg_slug_insert_update"
    BEFORE INSERT OR UPDATE ON public.decision
    FOR EACH ROW
    WHEN (NEW.title IS NOT NULL AND (NEW.slug IS NULL OR LEFT(NEW.slug, -7) != LEFT(public.slugify(NEW.title), -7)))
    EXECUTE PROCEDURE public.set_slug_from_title();