Re: Displaying text appears as hex data

Hello Thom,

On Tues., Feb 08, 2011, Thom Brown wrote:
>On 8 February 2011 12:45, Michael <postgresql(at)encambio(dot)com> wrote:
>> On Tues., Feb 08, 2011, Thom Brown wrote:
>>>On 8 February 2011 10:39, Michael wrote:
>>>> opensips=> select * from sip_trace;
>>>>  id | time_stamp | callid | traced_user | msg | method | ...
>>>>  1234 | 2011-02-03 | ... | | \x494e56495445207369703a...
>>>>
>>>> Others have said that when they use MySQL, the exact SQL command
>>>> as above results in ASCII text rather than hexadecimal, and this
>>>> is my goal as well.
>>>>
>>
>> opensips=> SELECT id, time_stamp, callid, traced_user,
>>           convert_from(msg, 'SQL_ASCII'), method FROM sip_trace;
>> ERROR:  function convert_from(text, unknown) does not exist
>> LINE 1: SELECT id, time_stamp, callid, traced_user, convert_from(msg...
>>                                                    ^
>>
>> The arrow in the last line indicates that 'convert_from' is not
>> correctly parsed.
>>
>My understanding was that your msg column was of type bytea. Is this
>not the case? Or is it a different column which needs converting?
>
The main developer has confirmed that the msg column is stored
as a BLOB, and I'm nearly sure that bytea is used in this case.
Yes, the correct column that is appearing in hex is called 'msg.'

Is the function 'convert_from' that you mentioned compiled into
the PostgreSQL server binary, or is it part of the template1 when
first created, or something else? It seems I'm missing it, right?

$ find postgresql-9.0.2.orig/src/backend/utils/mb/conversion_procs \
-exec grep -i bytea {} \; -print
...nothing

$ find postgresql-9.0.2.orig/src/backend/utils/mb/conversion_procs \
-exec grep -i hex {} \; -print
...nothing

$ find postgresql-9.0.2.orig/src/backend/utils/mb/conversion_procs \
-exec grep -i binary {} \; -print
static unsigned short BinarySearchRange
./euc_tw_and_big5/big5.c

...nope, not there either.

Regards,
Michael