-- "title" column should be in table which will be converted to slug.
-- Add column "slug" varchar unique null in table.


-- FUNCTION TO CREATE SLUG
-- also takes unicode

CREATE OR REPLACE FUNCTION public.slugify(title_text character varying)
RETURNS character varying
AS $function$
DECLARE
    result varchar;
BEGIN
    result := replace(title_text, '?', '');
   	result := replace(result , ':', '');
   	result := replace(result , '.', '');
  	result := replace(result , '“', '');
  	result := replace(result , '”', '');
  	result := replace(result , '"', '');
    result := replace(result , ')', '');
    result := replace(result , '(', '');
    result := replace(result , ',', '');
   	result := replace(result , '\', ' ');
   	result := replace(result , '/', ' ');
  	result := regexp_replace(replace(lower(result), ' ', '-'), E'[^\\w[[:alpha:]] -', '#', 'g');
	RETURN result;
end
$function$
LANGUAGE 'plpgsql'



--  FUNCTION TO GET UNIQUE SLUG
create or replace function slugify_on_insert()
returns trigger as
$$
DECLARE
    slug_text varchar;
    res varchar;
   	temp_text varchar;
   	c integer;
   	done boolean;
begin
	slug_text := slugify(new.title);
	temp_text := slug_text;
	c := 1;
  	done := false;
  	while not done loop
  		EXECUTE format('SELECT * FROM %I '
   		'WHERE slug = $1', TG_TABLE_NAME)
   		INTO res
   		USING temp_text;
		if res is not null then
			c := c+1;
			temp_text = slug_text || '-' || c;
			done := false;
  		else
  			if c > 1 then
  				slug_text := slug_text || '-' || c;
  			end if;
  			done := true;
  		end if;
  	end loop;

	new.slug = slug_text;
	Return new;
end
$$
language 'plpgsql'

-- TRIGGER ON INSERT
create trigger before_insert_trigger
before insert on table_name
for each row
execute procedure slugify_on_insert()