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
