Trigger Before Insertion.

trigger_slug_creator.sql

-- Date: May 12, 2020
--
-- RDBMS: Postgres.
-- Procedural Language: PL / pgSQL.
-- Given two params. create a slug (url identification) as new attr. for a row. 
--
-- Input: (a, a)
-- Output: 'a-a', if the tuple (a, a) do not exist.
--
-- if the tuple exist (a, a), then
-- Output: 'a-a-[2,3,...n]'

create or replace function create_slug(f_name text, lastname text default '')
    RETURNS text as 
$$
declare
    counter integer := 0;
    rslt text := '';
begin
    select  count(*) into counter 
    from  table t 
    where 
        t.name=f_name and t.last_name = lastname;
    
    if counter > 0  then
        select into rslt concat(lower(f_name), '-', lower(lastname), '-', (counter+1));
    else
        select into rslt concat(lower(f_name), '-', lower(lastname));
    end if;

    return rslt;
end; 
$$
language plpgsql;

-- select create_slug('a', 'dev');
-- drop function create_slug;

alter function create_slug(text, text)
    owner to realFranco;

create or replace function trigger_slug_creation() 
   returns trigger AS
$$ 
begin
    NEW.slug := create_slug(NEW.name, NEW.last_name);
    
    return NEW; 
end; 
$$
language plpgsql;

alter function trigger_slug_creation()
    owner to realFranco;


create trigger talent_slug 
    before insert
    on talent
    for each row
    execute procedure trigger_slug_creation();