On 8 February 2011 13:19, Michael <postgresql@encambio.com> wrote:
>
> 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.'
Well the function seems to think it's a text field.
> 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?
No, you're probably not missing it. It's complaining that a function
with the given signature (text, unknown), doesn't exist. It will work
if it matches (bytea, name). The error message being returned is
saying that the msg field is actually a text field.
Try:
SELECT pg_typeof(msg) FROM sip_trace LIMIT 1;
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935