Skip to content

Instantly share code, notes, and snippets.

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"
),
-- 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();
@kez

This comment has been minimized.

Copy link
Owner Author

kez commented May 13, 2019

Forked to remove single and double quotes so they don't get switched to hyphens. Also updated the trailing/leading hyphen regex as the \\- was not working for me ('\- does).

@nickreese

This comment has been minimized.

Copy link

nickreese commented Feb 14, 2020

How would you rework this to make sure the slug is unique and if not add a value at the end?

@kez

This comment has been minimized.

Copy link
Owner Author

kez commented Feb 14, 2020

At the moment the trigger and function is not aware of what table it's running against, so there's no immediately obvious way to check the uniqueness. You could use TG_RELID and pass through the table name to loop and check, but it's maybe not the best place for this logic.

A quick win would be to enforce uniqueness on the name column. Alternatively create the slug with the name and the primary key column:

NEW.slug := slugify(concat(NEW.name, '-', (NEW.id)::varchar);

Assuming you had a name of "Foo bar" and an ID of "1" this would give you "foo-bar-1" and the use of the primary key should always keep it unique, but you might not want to leak your primary key info like this.

@nickreese

This comment has been minimized.

Copy link

nickreese commented Feb 15, 2020

This is great. Thank you. I was looking to have another function that checked the table and looped through it to see if the value was unique but this is a good fit for now. You rock.

@augnustin

This comment has been minimized.

Copy link

augnustin commented Apr 29, 2020

Man, this is incredibly useful!! Thanks a lot, been searching for this for quite a while before I found your post: https://www.kdobson.net/2019/ultimate-postgresql-slug-function/

@kez

This comment has been minimized.

Copy link
Owner Author

kez commented Apr 29, 2020

Man, this is incredibly useful!! Thanks a lot, been searching for this for quite a while before I found your post: https://www.kdobson.net/2019/ultimate-postgresql-slug-function/

Thanks augnustin. There's a function below which checks for duplicates then adds an incrementing number until it's unique. Not that well tested and probably not best practice:

`
CREATE or replace FUNCTION public.set_slug_from_name() RETURNS trigger
LANGUAGE plpgsql
AS $function$
DECLARE
sql_string varchar;
tmp_slug varchar;
increment integer;
tmp_row record;
tmp_row_count integer;

BEGIN

tmp_row_count = 1;
increment = 0;
while tmp_row_count > 0 LOOP
  
  if increment > 0 then
    tmp_slug = slugify(NEW.name || ' ' || increment::varchar);
  ELSE
    tmp_slug = slugify(NEW.name);
  end if;

  sql_string = format(' select count(1) cnt from ' || TG_TABLE_NAME || ' where slug = ''' || tmp_slug || '''; ');
  for tmp_row in  execute(sql_string)
  loop
    raise notice '%', tmp_row;  
    tmp_row_count = tmp_row.cnt;  
  end loop;

increment = increment + 1;
END LOOP;

 NEW.slug := tmp_slug;
RETURN NEW;

END
$function$;
`

@augnustin

This comment has been minimized.

Copy link

augnustin commented Apr 29, 2020

Cheers @kez

I don't need that though, because in my case, I simply need to write things like select * from users u where slufigy(users.city) = 'paris';

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.