From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Vlad Romascanu <vromascanu(at)accurev(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT |
Date: | 2011-03-09 22:23:50 |
Message-ID: | AANLkTikKJ8eLs-eq-ckLybMt7dWeFkQ+THxizKVoRLz2@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Mar 9, 2011 at 4:09 PM, Vlad Romascanu <vromascanu(at)accurev(dot)com> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2011-03-09 22:27:13 | Re: pg_restore: [archiver] unsupported version (1.12) in file header |
Previous Message | Vlad Romascanu | 2011-03-09 22:09:34 | Reinterpreting BYTEA as TEXT, converting BYTEA to TEXT |