How can I return a UTF8 string from a hex representation in a latin9 database?

Поиск
Список
Период
Сортировка
От PGUser2020
Тема How can I return a UTF8 string from a hex representation in a latin9 database?
Дата
Msg-id E1r4oY5-00060N-4c@rmmprod06.runbox
обсуждение исходный текст
Ответы Re: How can I return a UTF8 string from a hex representation in a latin9 database?
Список pgsql-general
Hello. Hopefully I can explain myself

I have a database for a legacy application that requires an 8 bit database (i.e. the application itself won't function
ona UTF8 database). 

Looking at ways to extend the functionality to be able to handle a few specified fields in Unicode.

Had the idea to store a UTF8 string as either hex pairs or Base64 inside a VARCHAR field, which is fine. I can do that.

What needs to happen though, is to build a view, that will return the decoded hex (or b64) as a UTF8 string to a client
whichhas specified client encoding UTF8. 

I've tried various combinations of convert_from, and  convert_to, and convert, but I just can't seem to get it to
returnthe string a UTF8 select to the client. 

So if I have this data:

select * from mytable;
                 mycolumn
------------------------------------------
 ceb120ceb220ceb320ceb420ceb520cf83cf84

Then:

select convert_from(decode(mycolumn, 'hex')::bytea, 'utf-8') from mytable where usr='BATCH';

ERROR:  character with byte sequence 0xce 0xb1 in encoding "UTF8" has no equivalent in encoding "LATIN9"

So the database encoding is still relevant , this is expected by the description of convert_from in the documentation
ofcourse. 

Is there some combination of functions I can use to have a client select this column from this table in a LATIN9
databaseand get a UTF8 string back?   Any thoughts appreciated, thank you. 


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

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: Prepared statements versus stored procedures
Следующее
От: Erik Wienhold
Дата:
Сообщение: Re: How can I return a UTF8 string from a hex representation in a latin9 database?