470,648 Members | 1,571 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,648 developers. It's quick & easy.

PLPERL function error - utf-8 to iso8859-1

Trying to create a plperl function to strip non-friendly mainframe
characters from a string. However, when I try to add the Trademark symbol
(™) as a replace criteria, PG spits back an error:
ERROR: Could not convert UTF-8 to ISO8859-1
Any way I get around this?

CREATE OR REPLACE FUNCTION public.mainframe_description(varchar)
RETURNS varchar AS
'
my($desc) = @_;
$desc =~ s/®//g;
$desc =~ s//e/g;
$desc =~ s/ô/o/g;
$desc =~ s/æ/E/g;
$desc =~ s/Ò/ /g;
$desc =~ s/Ó/ /g;
$desc =~ s/è/e/g;
$desc =~ s/Ô//g;
$desc =~ s/É/E/g;
$desc =~ s/°//g;
$desc =~ s/é/e/g;
$desc =~ s/~Y//g;
$desc =~ s/Ö//g;
/*Error happens here */
$desc =~ s/™//g;
return $desc;
'
LANGUAGE 'plperl' VOLATILE;
COMMENT ON FUNCTION public.mainframe_description(varchar) IS 'Return a
clean description for mainframe input';
TIA

Patrick Hatcher

---------------------------(end of broadcast)---------------------------TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Nov 12 '05 #1
3 2401
"Patrick Hatcher" <PH******@macys.com> writes:
Trying to create a plperl function to strip non-friendly mainframe
characters from a string. However, when I try to add the Trademark symbol
(™) as a replace criteria, PG spits back an error:
ERROR: Could not convert UTF-8 to ISO8859-1


AFAICT this means that the trademark symbol is not in the character set
that you've specified to be used in the database; accordingly there's no
need to try to prevent it from being stored...

Perhaps you should have selected the database encoding to be the same as
what you're using on the client side.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #2

Ah there in lies the problem. I have my database encoding set to LATIN1
and I have this value stored in my table and can even write SQL to search
against it:
select *
from mdc_products
where description ~* '?'

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-422-1610 office
HatcherPT - AIM

Tom Lane
<tg*@sss.pgh.p To: "Patrick Hatcher" <PH******@macys.com>
a.us> cc: pg***********@postgresql.org
Subject: Re: [GENERAL] PLPERL function error - utf-8 to iso8859-1
10/14/2003
09:07 PM

"Patrick Hatcher" <PH******@macys.com> writes:
Trying to create a plperl function to strip non-friendly mainframe
characters from a string. However, when I try to add the Trademark symbol (") as a replace criteria, PG spits back an error:
ERROR: Could not convert UTF-8 to ISO8859-1


AFAICT this means that the trademark symbol is not in the character set
that you've specified to be used in the database; accordingly there's no
need to try to prevent it from being stored...

Perhaps you should have selected the database encoding to be the same as
what you're using on the client side.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

Ah there in lies the problem. I have my database encoding set to LATIN1
and I have this value stored in my table and can even write SQL to search
against it:
select *
from mdc_products
where description ~* '?'

Patrick Hatcher
Macys.Com
Legacy Integration Developer
415-422-1610 office
HatcherPT - AIM

Tom Lane
<tg*@sss.pgh.p To: "Patrick Hatcher" <PH******@macys.com>
a.us> cc: pg***********@postgresql.org
Subject: Re: [GENERAL] PLPERL function error - utf-8 to iso8859-1
10/14/2003
09:07 PM

"Patrick Hatcher" <PH******@macys.com> writes:
Trying to create a plperl function to strip non-friendly mainframe
characters from a string. However, when I try to add the Trademark symbol (") as a replace criteria, PG spits back an error:
ERROR: Could not convert UTF-8 to ISO8859-1


AFAICT this means that the trademark symbol is not in the character set
that you've specified to be used in the database; accordingly there's no
need to try to prevent it from being stored...

Perhaps you should have selected the database encoding to be the same as
what you're using on the client side.

regards, tom lane


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Daniel Hansen | last post: by
reply views Thread by Joshua D. Drake | last post: by
2 posts views Thread by Christopher Murtagh | last post: by
4 posts views Thread by Ed L. | last post: by
6 posts views Thread by Gianni Mariani | last post: by
10 posts views Thread by Robert Fitzpatrick | last post: by
reply views Thread by rski | last post: by
7 posts views Thread by Charles | last post: by
1 post views Thread by Korara | last post: by
reply views Thread by warner | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.