Hello Thom,

On Tues., Feb 08, 2011, Thom Brown wrote:
>On 8 February 2011 14:30, Michael <postgre...@encambio.com> wrote:
>> On Tues., Feb 08, 2011, Thom Brown wrote:
>>>On 8 February 2011 13:43, Thom Brown wrote:
>>>> On 8 February 2011 13:19, Michael wrote:
>>>>> On Tues., Feb 08, 2011, Thom Brown wrote:
>>>>>>On 8 February 2011 12:45, Michael 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.
>>>>>>>>>
>>>SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea,
>>>'SQL_ASCII'::name), method
>>>FROM sip_trace;
>>>
>> opensips=> SELECT id, time_stamp, callid, traced_user, \
>>  convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace;
>>  id   |     time_stamp      |          callid           | traced_user |      
>>                                                                              
>>                                                  convert_from                
>>                                                                              
>>                                         | method
>> -------+---------------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------
>>  30772 | 2011-02-08 15:04:34 | ab19ccbd8120-z92f7ean9o9c |             | 
>> INVITE sip:n...@name.host.tld;user=phone SIP/2.0\r                           
>>                                                                              
>>                                                                              
>>                                          +| INVITE
>>       |                     |                           |             | Via: 
>> SIP/2.0/TLS 192.168.100.123:1234;branch=asd84bK-rr8scd0jteop;rport\r         
>>                                                                              
>>                                                                              
>>                                        +|
>>       |                     |                           |             | 
>> From: "Username Person One" <sip:proxyu...@name.host.tld>;tag=thefromtag12\r 
>>                                                                              
>>                                                                              
>>                        +|
>>
>> There's a ton of blanks after '\r' the carriage returns. Is there
>> any way to construct a SELECT statement using filters or something
>> that will neatly format the msg field?
>>
>> Is there some stream editor similar function?
>>
>>  SELECT delwhitespace(s/convert_from(msg::bytea, 'SQL_ASCII'::name))
>>
>> Sorry to be pesky, since you already answered the original question.
>> Thanks again for doing that.
>>
>Well, you could always try:
>
>SELECT translate(convert_from(msg::bytea, 'SQL_ASCII'::name), E'\r\n', '');
>
Thanks alot that really helps, I'll figure the rest out from here.

Regards,
Michael

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to