Thread: Displaying text appears as hex data
Hello list,
I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
as BLOB and PostgreSQL is displaying it in hex format like so:
$ TERM=vt100 /pfx/bin/psql opensips opensips
psql (9.0.2)
Type "help" for help.
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.
How can I accomplish this in the easiest way?
Is there something in the PostgreSQL source tree
that I should change, for example hacking in:
src/backend/utils/mb/conversion_procs
Thanks alot,
Michael
On 8 February 2011 10:39, Michael <postgresql@encambio.com> wrote: > > Hello list, > > I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) > as BLOB and PostgreSQL is displaying it in hex format like so: > > $ TERM=vt100 /pfx/bin/psql opensips opensips > psql (9.0.2) > Type "help" for help. > > 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. ASCII text? You mean you wish to translate the binary into ASCII? You can use this: SELECT id, time_stamp, callid, traced_user, convert_from(msg, 'SQL_ASCII'), method FROM sip_trace; The reason why it doesn't automatically do this is because since it's binary data, it's up to you to define what its content format is. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
On Tue, Feb 08, 2011 at 11:39:04AM +0100, Michael wrote: > I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) > as BLOB I take it you mean BYTEA. > and PostgreSQL is displaying it in hex format like so: > > $ TERM=vt100 /pfx/bin/psql opensips opensips > psql (9.0.2) > Type "help" for help. > > 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. You might attempt to apply decode(column, 'hex') to the relevant column. Better be sure the result really is printable text. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Hello Karsten,
On Tues., Feb 08, 2011, Karsten Hilbert wrote:
>On 8 February 2011 10:39, Michael wrote:
>> I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
>> as BLOB
>
>I take it you mean BYTEA.
>
That's probably correct, yes.
>> and PostgreSQL is displaying it in hex format like so:
>>
>> $ TERM=vt100 /pfx/bin/psql opensips opensips
>> psql (9.0.2)
>> Type "help" for help.
>>
>> 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.
>>
>You might attempt to apply decode(column, 'hex') to the
>relevant column.
>
I had tried that before, and here's the result:
opensips=> SELECT id, time_stamp, callid, traced_user,
decode(msg, 'hex'), method FROM sip_trace;
ERROR: invalid hexadecimal digit: "\"
I don't understand this. Isn't it PostgreSQL that stores the BYTEA
values and then displays them in hex, indicating this by prepending
the '\x' backslash ex? Or if the '\x' is actually stored, then why
and who is doing that?
Any idea?
Thanks,
Michael
Hello Thom,
I sent this accidentally to you directly, here's a copy for the
list as well.
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.
>>
>ASCII text? You mean you wish to translate the binary into ASCII?
>You can use this:
>
>SELECT id, time_stamp, callid, traced_user, convert_from(msg,
>'SQL_ASCII'), method FROM sip_trace;
>
>The reason why it doesn't automatically do this is because since it's
>binary data, it's up to you to define what its content format is.
>
That's understandable and PostgreSQL is doing the right thing, but...
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.
Is it because I've not build something in:
src/backend/utils/mb/conversion_procs
I think I built a standard PostgreSQL installation, or?
Regards,
Michael
On 8 February 2011 12:45, Michael <postgresql@encambio.com> wrote: > > Hello Thom, > > I sent this accidentally to you directly, here's a copy for the > list as well. > > 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. >>> >>ASCII text? You mean you wish to translate the binary into ASCII? >>You can use this: >> >>SELECT id, time_stamp, callid, traced_user, convert_from(msg, >>'SQL_ASCII'), method FROM sip_trace; >> >>The reason why it doesn't automatically do this is because since it's >>binary data, it's up to you to define what its content format is. >> > That's understandable and PostgreSQL is doing the right thing, but... > > 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? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
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
On Tue, Feb 08, 2011 at 02:19:49PM +0100, Michael wrote: > >> 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, That is not really possible since PostgreSQL does not have a "BLOB" data type as such. It would either be a BYTEA column or a Large Object (which would live elsewhere and not inside the table you work on). > and I'm nearly sure that bytea is used in this case. Very likely. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
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
On 8 February 2011 13:43, Thom Brown <thom@linux.com> wrote: > 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; Or if it really is text format: SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea, 'SQL_ASCII'::name), method FROM sip_trace; But then that's less efficient than storing it as bytea and slower to query. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Hello Karsten, On Tues., Feb 08, 2011, Karsten Hilbert wrote: >On Tue, Feb 08, 2011 at 02:19:49PM +0100, Michael wrote: >>>> 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, > >That is not really possible since PostgreSQL does not have a >"BLOB" data type as such. > >It would either be a BYTEA column or a Large Object (which >would live elsewhere and not inside the table you work on). > >> and I'm nearly sure that bytea is used in this case. >> >Very likely. > Okay, after looking at the sources I can confirm that BLOB maps indeed to the 'bytea' PostgreSQL data type. Regards, Michael
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. >>>>>>> >>>>> 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. >> Okay, maybe I missed something and thought it was a bytea. >>> 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; >> opensips=> SELECT pg_typeof(msg) FROM sip_trace LIMIT 1; pg_typeof ----------- text (1 row) ...so you are right. Its not a bytea after all. >Or if it really is text format: > >SELECT id, time_stamp, callid, traced_user, convert_from(msg::bytea, >'SQL_ASCII'::name), method >FROM sip_trace; > That worked very well, thanks. Now that I can read the text, I see that it is very poorly formatted. I'll write about that problem in another email. >But then that's less efficient than storing it as bytea and slower >to query. > I'll pass that on to the OpenSIPS database developer, thanks. Regards, Michael
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@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:Proxyuser@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.
Regards,
Michael
On 8 February 2011 14:30, Michael <postgresql@encambio.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@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:Proxyuser@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', ''); -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Hello Thom, On Tues., Feb 08, 2011, Thom Brown wrote: >On 8 February 2011 14:30, Michael <postgresql@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:num@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:Proxyuser@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
Michael <postgresql@encambio.com> wrote: > > Hello list, > > I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) > as BLOB and PostgreSQL is displaying it in hex format like so: > > $ TERM=vt100 /pfx/bin/psql opensips opensips > psql (9.0.2) > Type "help" for help. > > opensips=> select * from sip_trace; > id | time_stamp | callid | traced_user | msg | method | ... > 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... set: bytea_output = 'escape' Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > Michael <postgresql@encambio.com> wrote: > >> >> Hello list, >> >> I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) >> as BLOB and PostgreSQL is displaying it in hex format like so: >> >> $ TERM=vt100 /pfx/bin/psql opensips opensips >> psql (9.0.2) >> Type "help" for help. >> >> opensips=> select * from sip_trace; >> id | time_stamp | callid | traced_user | msg | method | ... >> 1234 | 2011-02-03 | ... | | \x494e56495445207369703a... > > set: > > bytea_output = 'escape' That won't help as the msg column is actually text... for some reason. And they want to see the converted ASCII text based on the hex-represented binary data. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Thom Brown <thom@linux.com> wrote:
> On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> > Michael <postgresql@encambio.com> wrote:
> >
> >>
> >> Hello list,
> >>
> >> I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest)
> >> as BLOB and PostgreSQL is displaying it in hex format like so:
^^^^
> >
> > set:
> >
> > bytea_output = 'escape'
>
> That won't help as the msg column is actually text... for some reason.
Are you sure? I know that problem from DRUPAL with 9.0.
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On 8 February 2011 19:28, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > Thom Brown <thom@linux.com> wrote: > >> On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote: >> > Michael <postgresql@encambio.com> wrote: >> > >> >> >> >> Hello list, >> >> >> >> I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) >> >> as BLOB and PostgreSQL is displaying it in hex format like so: > ^^^^ > >> > >> > set: >> > >> > bytea_output = 'escape' >> >> That won't help as the msg column is actually text... for some reason. > > Are you sure? I know that problem from DRUPAL with 9.0. I asked Michael to confirm the column type of msg and it turned out to be text. No mention of the lo contrib module, so I guess that may possibly come into play, but I wouldn't know about that. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935
Thom Brown <thom@linux.com> wrote: > On 8 February 2011 19:28, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > > Thom Brown <thom@linux.com> wrote: > > > >> On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote: > >> > Michael <postgresql@encambio.com> wrote: > >> > > >> >> > >> >> Hello list, > >> >> > >> >> I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) > >> >> as BLOB and PostgreSQL is displaying it in hex format like so: > > ^^^^ > > > >> > > >> > set: > >> > > >> > bytea_output = 'escape' > >> > >> That won't help as the msg column is actually text... for some reason. > > > > Are you sure? I know that problem from DRUPAL with 9.0. > > I asked Michael to confirm the column type of msg and it turned out to > be text. No mention of the lo contrib module, so I guess that may > possibly come into play, but I wouldn't know about that. Okay, you are right, i haven't read the other sub-thread, sorry. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Tues., Feb 08, 2011, Thom Brown wrote: >On 8 February 2011 19:28, Andreas Kretschmer <akretschmer@spamfence.net> wrote: >> Thom Brown <thom@linux.com> wrote: >>> On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote: >>>> Michael <postgresql@encambio.com> wrote: >>>>> I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) >>>>> as BLOB and PostgreSQL is displaying it in hex format like so: >> ^^^^ >> >>>> set: >>>> >>>> bytea_output = 'escape' >>>> >>> That won't help as the msg column is actually text... for >>> some reason. >> >> Are you sure? I know that problem from DRUPAL with 9.0. >> >I asked Michael to confirm the column type of msg and it turned out to >be text. No mention of the lo contrib module, so I guess that may >possibly come into play, but I wouldn't know about that. > By the way if the data is indeed properly stored as text in the database, why isn't it printed as text when using a plain SELECT? The text data is printed as binary hex instead, what?! ...but the conversion works of course. Regards, Michael
On 8 February 2011 22:27, Michael <postgresql@encambio.com> wrote: > > On Tues., Feb 08, 2011, Thom Brown wrote: >>On 8 February 2011 19:28, Andreas Kretschmer <akretschmer@spamfence.net> wrote: >>> Thom Brown <thom@linux.com> wrote: >>>> On 8 February 2011 18:45, Andreas Kretschmer <akretschmer@spamfence.net> wrote: >>>>> Michael <postgresql@encambio.com> wrote: >>>>>> I'm trying to view text data stored by OpenSIPS 1.6.4 (the latest) >>>>>> as BLOB and PostgreSQL is displaying it in hex format like so: >>> ^^^^ >>> >>>>> set: >>>>> >>>>> bytea_output = 'escape' >>>>> >>>> That won't help as the msg column is actually text... for >>>> some reason. >>> >>> Are you sure? I know that problem from DRUPAL with 9.0. >>> >>I asked Michael to confirm the column type of msg and it turned out to >>be text. No mention of the lo contrib module, so I guess that may >>possibly come into play, but I wouldn't know about that. >> > By the way if the data is indeed properly stored as text in the > database, why isn't it printed as text when using a plain SELECT? > The text data is printed as binary hex instead, what?! > > ...but the conversion works of course. That's why I'm a bit miffed, because you've stored a textual representation of the binary data in hex. In other words, when it says '\x494e56495445207369703a', it's stored as that bit of text. It's not outputting a hexadecimal representation of the data, the hexadecimal representation is the actual data. So you could have '\x494ePostgreSQL rules', which obviously wouldn't be valid for casting to a bytea, but it's perfectly valid text. The question is, how is that data getting in there? There's probably binary data with a ::text conversion going on during insert. What you appear to want is the original textual representation going in as a text field since you're converting all the time during the SELECT, but it's being put into binary then cast to text before getting to your table. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935