Slugify postgres functions for creating slugs from arbitrary text strings

create_unaccent_extension.sql

CREATE EXTENSION IF NOT EXISTS "unaccent";

slugify.sql

CREATE OR REPLACE FUNCTION slugify(input text) RETURNS text
AS $$
BEGIN
  input := unaccent(input);
  input := lower(input);
  -- remove single and double quotes
  input := regexp_replace(input, '[''"]+', '', 'gi');
  -- replace anything that's not a letter, number, hyphen, or underscore with a hyphen
  input := regexp_replace(input, '[^a-z0-9\-_]+', '-', 'gi');
  -- trim hyphens if they exist on the ends of the string
  input := regexp_replace(input, '\-+$', '');
  input := regexp_replace(input, '^\-', '');
  RETURN input;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE PARALLEL SAFE;

slugify_unique.sql

CREATE OR REPLACE FUNCTION slugify(input text, table_name text, column_name text) 
RETURNS text AS $$
DECLARE
    base_slug text;
    final_slug text;
    counter integer := 1;
    max_length integer := 15;
    existing_slug text;
BEGIN
    -- Generate the initial base slug
    base_slug := unaccent(input);
    base_slug := lower(base_slug);
    -- Remove single and double quotes
    base_slug := regexp_replace(base_slug, '[''"]+', '', 'gi');
    -- Replace anything that's not a letter, number, hyphen, or underscore with a hyphen
    base_slug := regexp_replace(base_slug, '[^a-z0-9\-_]+', '-', 'gi');
    -- Trim hyphens from the ends
    base_slug := regexp_replace(base_slug, '\-+$', '');
    base_slug := regexp_replace(base_slug, '^\-', '');
    
    -- Truncate the base slug to leave room for the counter
    -- Max length minus 4 characters (for potential "-999" suffix)
    base_slug := substring(base_slug, 1, max_length - 4);
    
    -- Remove any trailing hyphen after truncation
    base_slug := regexp_replace(base_slug, '\-+$', '');
    
    -- Try the base slug first
    final_slug := base_slug;
    
    -- Check if the slug exists using dynamic SQL
    LOOP
        EXECUTE format('SELECT %I FROM %I WHERE %I = $1 LIMIT 1', 
                      column_name, table_name, column_name)
        INTO existing_slug
        USING final_slug;
        
        EXIT WHEN existing_slug IS NULL;
        
        -- If slug exists, append counter and increment
        final_slug := base_slug || '-' || counter;
        counter := counter + 1;
        
        -- Ensure the final slug with counter doesn't exceed max length
        IF length(final_slug) > max_length THEN
            -- Truncate base_slug further to accommodate counter
            base_slug := substring(base_slug, 1, max_length - length(counter::text) - 1);
            final_slug := base_slug || '-' || counter;
        END IF;
    END LOOP;
    
    RETURN final_slug;
END;
$$ LANGUAGE plpgsql STRICT;