Re: Displaying text appears as hex data

On 8 February 2011 14:30, Michael <postgresql(at)encambio(dot)com> wrote:
>
> Hello Thom,
>
> 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:num(at)name(dot)host(dot)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:Proxyuser(at)name(dot)host(dot)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', '');

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935