On 8 February 2011 13:43, Thom Brown <thom@xxxxxxxxx> wrote: > On 8 February 2011 13:19, Michael <postgresql@xxxxxxxxxxxx> wrote: >> >> Hello Thom, >> >> On Tues., Feb 08, 2011, Thom Brown wrote: >>>On 8 February 2011 12:45, Michael <postgresql@xxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
