Untitled

--DROP FUNCTION public.f_get_phone_format_test(text);
--SELECT public.f_get_phone_format_test(:local_number);

create or replace function public.f_get_phone_format_test(
    local_number text --локальный номер телефона в любом формате или NULL
)
    returns text
    immutable
    language plpgsql
    set search_path = ''
    cost 10
as
$$
declare
    phone text;
    --is_starts_with_plus boolean;
begin
   
    local_number := trim(regexp_replace(local_number, '(?:^\D+|\D+$|[ (); \-.]+|&(?:ndash|minus);)', ' ', 'g'));
    
    phone := replace((local_number), ' ', '');
    phone := regexp_replace(phone, '(?<![а-яё])[сc]\d+[дп]о\d+ч?', '');
    phone := regexp_replace(phone, '(?<=\d)(доб|вн|ext)\d+$', ''); --добавочный номер
    phone := regexp_replace(phone, '(?<=\d)(/\d\d)+$', ''); --альтернативные номера через слэш
    phone := replace(phone, '/', '');
   
    return phone;
end;
$$;

select public.f_get_phone_format_test('+7 (977) 123-45-67');
select public.f_get_phone_format_test('+7 (977) 123-45-67'::text);
select public.f_get_phone_format_test(null::text);
select public.f_get_phone_format_test(null);