Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT

Поиск
Список
Период
Сортировка
От Vlad Romascanu
Тема Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT
Дата
Msg-id AANLkTik9CrsnhmLb-zm9uqVo-Qx4GsjUrdzDsJdt5z-z@mail.gmail.com
обсуждение исходный текст
Ответы Re: Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT
Список pgsql-general
Hello,

I need to perform "conversions" (transcoding) between BYTEA and TEXT
columns in a UTF-8 database.  I searched for existing solutions and
was unable to find one for 8.x or 9.x, so I cam up with something I'd
like to validate with the more enlightened members of this list...

Case 1: reinterpreting:

(Working on a 8.4.3 backend.)

In a UTF8 database I have a BYTEA value which contains a perfectly
valid UTF8 string.  I want to simply *reinterpret* it as TEXT (in the
same way pg_convert_from internally reinterprets the BYTEA return
value from pg_convert as TEXT), backend-side, no
transcoding/encoding/decoding should take place.

The solution I came up with goes something like this:

  CREATE DOMAIN my_varlena AS bytea;
  CREATE CAST (my_varlena AS text) WITHOUT FUNCTION;
  ...
  SELECT bytea_col::my_varlena::text FROM tbl; -- bypass the bytea to
varchar/text conversion which actually calls encode()
  ...
  DROP DOMAIN my_varlena CASCADE;

Is there anything blatantly wrong with this approach that I have
missed, or is there a more straightforward way, or anything to be
improved?  (Again, I need a backend-side solution, not a client-side
one -- e.g. copying huge amounts of data from a BYTEA column to a TEXT
column in some other table.)



Case 2: converting:

(Working on a 8.4.3 backend.)

In a UTF8 database I have a BYTEA value which contains a perfectly
valid e.g. LATIN1 string.  Building on top of the above (again, 100%
backend-side), is there anything blatantly wrong with:

  CREATE DOMAIN my_varlena AS bytea;
  CREATE CAST (my_varlena AS text) WITHOUT FUNCTION;
  ...
  SELECT convert(bytea_col::bytea, 'LATIN1', 'UTF8')::my_varlena::text FROM tbl;
  ...
  DROP DOMAIN my_varlena CASCADE;


Thank you!

PS: Incidentally, if bytea_col contains any of the invalid LATIN1
sequences 0x7f, 0x80, 0x81, 0x82 etc., pg_convert (v8.4.3) will
(leniently) convert them to the invalid UTF-8 BYTEA sequences 0x7f,
0xc2 0x80, 0xc2 0x81, 0xc2 0x82 etc. :)

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

Предыдущее
От: John Edens
Дата:
Сообщение: Re: How to configure for remote TCP/IP client conncections using MS Visual Basic OLE DB calls and PostgreSQL dll's?
Следующее
От: Merlin Moncure
Дата:
Сообщение: Re: Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT