CREATE OR REPLACE FUNCTION test.phoneme(
	in_lexeme text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    IMMUTABLE 
    
AS $BODY$
declare
  res varchar(100) DEFAULT '';
begin
  res := lower(in_lexeme);
  res := regexp_replace(res,'[ъь]','','g');
  res := regexp_replace(res,'(йо|ио|йе|ие)','и','g');
  res := regexp_replace(res,'[оыя]','а','g');
  res := regexp_replace(res,'[еёэ]','и','g');
  res := regexp_replace(res,'ю','у','g');
  res := regexp_replace(res,'б([псткбвгджзфхцчшщ]|$)','п\1','g');
  res := regexp_replace(res,'з([псткбвгджзфхцчшщ]|$)','с\1','g');
  res := regexp_replace(res,'д([псткбвгджзфхцчшщ]|$)','т\1','g');
  res := regexp_replace(res,'в([псткбвгджзфхцчшщ]|$)','ф\1','g');
  res := regexp_replace(res,'г([псткбвгджзфхцчшщ]|$)','к\1','g');  
  res := regexp_replace(res,'дс','ц','g');
  res := regexp_replace(res,'тс','ц','g');
  res := regexp_replace(res,'(.)\1','\1','g');
  return res;
exception
  when others then raise exception '%', sqlerrm;
end;
$BODY$;

CREATE OR REPLACE FUNCTION test.metaphone(
	in_phonemes text)
    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    IMMUTABLE 
    
AS $BODY$
begin
  return (
    select string_agg(q.lex,' ') from (
      select test.phoneme(lexeme) as lex from unnest(to_tsvector('simple', in_phonemes))	
      order by positions
    ) as q
  );
exception when others then
  raise '%', SQLERRM using errcode = SQLSTATE;
end;
$BODY$;

SELECT 
name,
SIMILARITY(test.metaphone(name),test.metaphone('Ответ на все сущее'))
FROM test.sample
order by 
2 desc
LIMIT 100;