mbverifystr-multibyte.sql

-- To use, save http://kermitproject.org/utf8.html as
-- '/tmp/UTF-8 Sampler.html' for test data.
--
-- 'chinese62.txt' is 62kB of Mandarin-language text (origin no longer known).

-- Call convert_from() to perform UTF-8 verification on input data.
-- Repeat 5 times, return the time elapsed in ms of the fastest run.
create or replace function time_verifystr(testdata bytea)
returns double precision language plpgsql as $$
declare
  begin_ts timestamptz;
  end_ts timestamptz;

  diff interval;
  min_diff interval;

begin
  for i in 1..5 loop
    begin_ts := clock_timestamp();

    perform bool_or(convert_from(testdata, 'utf8') = 'x') from generate_series(1, 10000);

    diff := clock_timestamp() - begin_ts;

    if diff < min_diff or min_diff is null then
       min_diff = diff;
    end if;
  end loop;

  return extract(milliseconds from min_diff);
end;
$$;

select 
round(time_verifystr(pg_read_binary_file('/path/to/Chinese62.txt'))) as chinese,
round(time_verifystr(pg_read_binary_file('/path/to/UTF-8 Sampler.htm'))) as mixed,
round(time_verifystr(repeat('a', 62000)::bytea)) as ascii;