Scans all text values from the given table/column names and, given a text prefix, finds the maximum natural number prefixed with that prefix.

extract_max_number_from_prefixed_value.sql

drop function if exists maybe_to_natural_number;
create or replace function maybe_to_natural_number(str text) returns integer
  language plpgsql immutable leakproof returns null on null input parallel safe as $$
  begin
    if regexp_match(str, '^\d+$') is null then
      return null;
    end if;
    return str::int8;
  end;
$$;

drop function if exists extract_max_number_from_prefixed_value;
create or replace function extract_max_number_from_prefixed_value(
  table_name text,
  column_name text,
  text_value_prefix text,
  out max_index_found integer
) language plpgsql as $$
  begin

    execute format('
select max(
  case when starts_with(%I, $1) then maybe_to_natural_number(trim(substring(%I, char_length($2)+1))) else null end
)
from %I',
      column_name,
      column_name,
      table_name
    ) into max_index_found
      using text_value_prefix, text_value_prefix;
  end;
$$;