UTF-8 and stripping accents

Поиск
Список
Период
Сортировка
От Christopher Murtagh
Тема UTF-8 and stripping accents
Дата
Msg-id 92fbb7920606151008x2838b627tab5f55bb6e7c56b4@mail.gmail.com
обсуждение исходный текст
Список pgsql-general
Greetings folks,

 I'm trying to write a stored procedure that strips accents from UTF-8
encoded text. I saw a thread on this list discussing something very
similar to this on April 8th, and used it to start. However, I'm
getting odd behaviour.

My stored procedure:

CREATE OR REPLACE FUNCTION strip_accents(text) RETURNS text
AS '

 use Unicode::Normalize;
 use Encode;

 my $string = NFD( decode( utf8 => $_[0] ) );

 $string =~ s/\p{Mn}+//ogsm;
 return NFC($string);

'
LANGUAGE plperlu;

I'm trying this on two different postgres dbs. One is pg 7.4.6, the
other is 8.1.4 and they both break in different ways.

On the 8.1.4:
test=# select strip_accents('This is Québec, français, noël, à la mode');
-[ RECORD 1 ]-+------------------------------------------
strip_accents | This is Qu�bec, fran�ais, no�l, � la mod

(not sure how this will arrive to the list, but basically all accented
characters are repliaced with a cedile)

and if I try a 'select strip_accents(column) from table;' in a UTF8
encoded database I get:

ERROR:  error from Perl function: Cannot decode string with wide
characters at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/Encode.pm
line 166.

On the 7.4.6, I simply get the input without any changes for both the
direct input and for a column.

test=# select strip_accents('This is Québec, français, noël, à la mode');
-[ RECORD 1 ]-+------------------------------------------
strip_accents | This is Québec, français, noël, à la mod

Now, on both of these machines, I have the following simple perl script:

[chris@mafalda ~]$ cat strip_accents.pl
#!/usr/bin/perl

 use Unicode::Normalize;
 use Encode;

 my $string = NFD( decode( utf8 => $ARGV[0] ) );

 $string =~ s/\p{Mn}+//ogsm;

 print NFC($string)."\n";

When executed, it behaves as expected:

[chris@mafalda ~]$ ./strip_accents.pl 'This is Québec, français, noël,
à la mode'
This is Quebec, francais, noel, a la mode

So, I'm obviously doing something dumb/wrong with encodings, but I
can't for the life of me figure it out. I've tried setting client
encodings, verifying database encodings, etc.. all to no avail. Is
there something obvious that I'm missing? Is there a better way to
achieve what I'm trying to do?

Thanks in advance for any insight.

Cheers,

Chris

В списке pgsql-general по дате отправления:

Предыдущее
От: "Relyea, Mike"
Дата:
Сообщение: Re: Out of memory error in 8.1.0 Win32
Следующее
От: Bruce Momjian
Дата:
Сообщение: Re: A few questions about carriage returns (\r)