Hello Thom,
On Tues., Feb 08, 2011, Thom Brown wrote:
>On 8 February 2011 12:45, Michael <postgresql@encambio.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
