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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general