Re: Binary bytea to literal strings

Dragan Zubac wrote:
> Hello
>
> Got one table:
>
> Column | Type | Modifiers
> ---------+---------+-----------------------------------------------
> message | bytea |
>
> insert data like for example:
>
> insert into zub (message) values (E'\004\065 Ciao');
>
> is it possible to somehow get the following:
>
> 0x040x35 Ciao

Not without using a function that knows how your data is structured.
\065 is, in the ASCII encoding, the digit '5'. The only function that
might've done what you wanted would be something that interpreted the
bytea as an extended ASCII string and printed hex escapes for characters
not in the ASCII printable range. That won't work if some of the values
you wish to escape are printable.

Personally, as you appear to want to treat the two parts of the message
differently, I'd split the value up into a text and bytea part and store
it in those two separate fields. I'd then provide a formatting function
that took the two parts as arguments and returned a string where the
first part is represented as hex escapes and the second part as literal
text.

For how to get integer values for characters, hex-escape bytea values,
etc, see:

http://www.postgresql.org/docs/current/static//functions-binarystring.html

I'd probably wrap it all up in a composite type with CREATE TYPE, but
you may not want to bother with that - it's a bit of hassle creating the
operator class, ordering functions, etc.

On a side note, is there any way I can get Pg to automatically generate
an operator class and set of comparison operators for a composite type?

I've run into a few situations where it'd be nice to do things like
compare two rows element by element, first to last, or create an index
on a simple composite type that's naturally sorted by
(first,second,....) . Most cases are sorted out easily enough by
wrapping the records in a row constructor, eg

ROW(rec1.*) < ROW (rec2.*)

but it seems like it'd make sense to be able to CREATE TYPE to get a
named composite type with the same operator behavior as is implicit in
ROW(...) comparisons. What's trivial with a row constructor requires the
writing of an operator class and a bunch of comparison functions to do
with a named composite type.

--
Craig Ringer