Hi,
I'm migrating a PG 7.4 database (encoded in LATIN1) to PG 9.1. The upgrade is
just fine, except a problem with a conversion to UNICODE for which I was not
able to find a solution yet:
I have a function under PG 7.4 that returns an xml structure, with the content
encoded in UNICODE.
The function is like:
---------------------------------------------
CREATE OR REPLACE FUNCTION get_xml()
RETURNS text AS
$$
DECLARE
output text;
BEGIN
-- We fill the output variable with xml
...
-- We return it in unicode
RETURN convert(output, 'LATIN1', 'UTF8');
END;
$$
LANGUAGE 'plpgsql';
---------------------------------------------
After migrating to PG 9.1, I noticed that convert now requires a bytea, and not
a text. I tried different things:
- Change the output variable to a bytea
- Use RETURN convert(convert_to(output, 'LATIN1'), 'LATIN1', 'UTF8');
- Encode the database in UTF8 instead of LATIN1
... but no output is similar to what I had under PG 7.4.
More precisely, I had under PG 7.4 something like (notice the Name Hélène
converted into Hélène)
"<?xml version="1.0" encoding="UTF-8"?>
<DispatchAuftrag>
<Versicherungsnehmer>
<Name>Martelli</Name>
<Vorname>Hélène</Vorname>
<Strasse>rue des Comptes</Strasse>
<Land>Suisse</Land>
<PLZ>123456</PLZ>
<Ort>Fribourg</Ort>
<Email>.</Email>
<TelMobil>.</TelMobil>
<TelPrivat>.</TelPrivat>
<TelGeschaeft>.</TelGeschaeft>
<Fax>.</Fax>
</Versicherungsnehmer>
</DispatchAuftrag>
"
And now I get something like:
"<?xml version="1.0" encoding="UTF-8"?>\012<DispatchAuftrag>\012
</Versicherungsnehmer>\012 <Geschaedigter>\012 <Name>Etat du
Valais</Name>\012 <Vorname></Vorname>\012 <Strasse>Indivis / Centre
entretien Autoroute</Strasse>\012 <Land>Suisse</Land>\012
<PLZ>1906</PLZ>\012 <Ort>Charrat</Ort>\012 <Email></Email>\012
<TelMobil></TelMobil>\012 <TelPrivat>027 747 61 00</TelPrivat>\012
<TelGeschaeft>.</TelGeschaeft>\012 <Fax></Fax>\012 </Geschaedigter>\012
<Schadendaten>\012 <SchadenDatum>2005-01-23</SchadenDatum>\012
<SchadenNr>JR/41123-208/JPS</SchadenNr>\012
<GeschaetzteSchadenhoehe></GeschaetzteSchadenhoehe>\012
<SchadenAmFz>Dommages aux installations routi\303\250res</SchadenAmFz>\012
<Bemerkung></Bemerkung>\012 </Schadendaten>\012 <Fahrzeugstandort>\012
<Name></Name>\012 <Vorname></Vorname>\012 <Strasse></Strasse>\012
<Land></Land>\012 <PLZ></PLZ>\012 <Ort></Ort>\012
<Telefon></Telefon>\012 <Fax></Fax>\012 <Email></Email>\012
</Fahrzeugstandort>\012</DispatchAuftrag>\012"
Newlines don't seem to be handled properly, and I'm unable to find out how to
change that. UTF8 encoding is not good either.
Any idea how to correct that?
Thanks!
Philippe
-------------------------------------------------------------
Attik System web : http://www.attiksystem.ch
Philippe Lang phone : +41 26 422 13 75
rte de la Fonderie 2 gsm : +41 79 351 49 94
1700 Fribourg twitter: @philippelang
pgp : http://keyserver.pgp.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
