Generates a unique username based on the first and last name entered.

unique_username.sql

drop trigger if exists unique_username on users;
drop function if exists unique_username();
drop table if exists users;

create table users (
  id serial primary key,
  username varchar unique,
  name varchar not null,
  surname varchar not null,
  check (username is not null and username != '')
);

create or replace function unique_username() returns trigger as
$$
declare
  unique_username varchar;
  i integer;
begin
  unique_username := coalesce(new.username, LOWER(CONCAT(new.name, new.surname)));
  unique_username := TRANSLATE(unique_username, 'çğıöşü', 'cgiosu');
  new.username = unique_username;
  i := 1;
  while exists(select * from users where username = new.username) loop
    new.username = TRANSLATE(unique_username, 'çğıöşü', 'cgiosu') || i;
    i := i + 1;
  end loop;
  return new;
end
$$ language plpgsql;

create trigger unique_username
before insert on users
for each row execute procedure unique_username();

insert into users(name,surname) values ('mehmet','seven'), ('mehmet', 'seven');
insert into users(username, name, surname) values ('mehmetseven', 'mehmet', 'seven');