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
