generating slugs in postgres

slugify.sql

BEGIN;

DROP FUNCTION IF EXISTS slugify CASCADE;
DROP TRIGGER IF EXISTS slugify
ON talks;
DROP TABLE IF EXISTS talks CASCADE;

CREATE
OR REPLACE FUNCTION slugify(intake TEXT)
  RETURNS TEXT AS
$function$
SELECT regexp_replace(regexp_replace(lower(intake), '[.,\/#!$%\^&\*;:{}=_`~()]', '', 'g'), '[^a-z0-9\\-_]+', '-', 'g');
$function$
LANGUAGE SQL
STRICT
IMMUTABLE;

CREATE TABLE IF NOT EXISTS talks (
  id   BIGSERIAL   NOT NULL,
  name VARCHAR(32) NOT NULL,
  slug VARCHAR(60),
  CONSTRAINT pk_talks PRIMARY KEY (id)
);

CREATE OR REPLACE FUNCTION talks_bit() 
  RETURNS trigger LANGUAGE plpgsql AS
$function$
BEGIN
  NEW.slug := slugify(NEW.name);
  RETURN NEW;
END
$function$;

CREATE TRIGGER talks_bit
  BEFORE INSERT
  ON talks
  FOR EACH ROW
  -- WHEN (NEW.name IS NOT NULL AND NEW.slug IS NULL)
EXECUTE PROCEDURE talks_bit();

END;

INSERT INTO talks (name)
VALUES ('Earthquake_-001, signatures_!');
-- SELECT * FROM talks;

slugiify-README.md

ichux

by ichux