嗯。我想这样可以：

select 'bjones' || coalesce(lpad( ((regexp_match(max(username), '[0-9]+$'))[1]::int + 1)::text, 5, '0'), '00000')
from t
where username ~ '^bjones[0-9]{5}$';

这是一把小提琴。

更严格和参数化，具有4位序列号。

with 
unp as -- user name prefix
(select left(lower(:givenname), 1) || lower(:surname) unp),
maxno as -- max existing seq.number for this username prefix
(
    select coalesce(max((regexp_match(username, '(\d+)$'))[1]::integer), 0) maxno 
    from usernames
    where username ~ ('^'||(select unp from unp)||'\d+$')
)   
select (select unp from unp) || to_char((select maxno + 1 from maxno), 'FM0009')  username;

我对以下代码做了类似的操作：

_username = left(lower(_firstname), 1) || lower(_lastname);
PERFORM id FROM users WHERE username = _username;
IF FOUND THEN
  FOR i in 1 .. 999
  LOOP
    _username := _username || i;
    PERFORM id FROM users WHERE username = _username;
    IF NOT FOUND THEN
      EXIT;
    END IF;
  END LOOP;
END IF;