On Wed, Mar 9, 2011 at 4:09 PM, Vlad Romascanu <vromascanu@xxxxxxxxxxx> wrote: > 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.) I think convert_from is a little more direct: convert_from(string bytea, src_encoding name) http://www.postgresql.org/docs/8.4/interactive/functions-string.html merlin -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
