Oops! You have to write a function for it. Here is a
sample quick C function:
typedef struct
{ int len;
char data[1];
} string;
string *byteatostr(bytea *arg)
{
char buf[1024];
int ln,i;
string *res;
ln = VARSIZE(arg) - VARHDRSZ;
memmove(buf, VARDATA(arg), ln);
for(i=0; i < ln; i++)
if( buf[i] < 32 ) buf[i]='?';
res = (string *) palloc(VARHDRSZ + ln);
memset(res, 0, VARHDRSZ + ln);
res->len = VARHDRSZ + ln;
memmove(res->data, buf, (int) ln);
return res;
}
where all non-printable chars are replaced with '?'. Optionally,
the chars can be escaped (prefixed with '\') - adjusting ln as neccessry.
now you get:
# select byteatostr(col) from test2;
byteatostr
------------------------------------------------------------
fk_uste_wu_id?us_states?web_users?UNSPECIFIED?wu_id?wu_id?
(1 rows)
'hope it works for you.
Morey Parang
ORNL
On Tue, Aug 15, 2000 at 05:23:38PM -0400, mikeo wrote:
> thanks anyway but that doesn't work. i should've mentioned that i'd
> already tried that in SQL. my apologies.
>
>
> tig4=# \d x
> Table "x"
> Attribute | Type | Modifier
> -----------+-------+----------
> tgargs | bytea |
>
> tig4=# select substr(tgargs,1,5) from x;
> ERROR: Function 'substr(bytea, int4, int4)' does not exist
> Unable to identify a function that satisfies the given argument types
> You may need to add explicit typecasts
>
> tig4=# select substr(tgargs::text,1,5) from x;
> ERROR: Cannot cast type 'bytea' to 'text'
>
> and other things like varchar, etc.
>
> tig4=# select * from x;
> tgargs
> ------------------------------------------------------------------------------
> fk_uste_wu_id\000us_states\000web_users\000UNSPECIFIED\000wu_id\000wu_id\000
> fk_uste_wu_id\000us_states\000web_users\000UNSPECIFIED\000wu_id\000wu_id\000
> fk_uste_wu_id\000us_states\000web_users\000UNSPECIFIED\000wu_id\000wu_id\000
>
> what i'm looking to do here is to get the table names and column names out
> using sql. i can do it in perl with a split command on '\' but was curious
> as to how to "SQL" split up a BYTEA type field.
>
> mikeo
>
>
> At 04:38 PM 8/15/00 -0400, mjp@ornl.gov wrote:
> >Try
> >
> >substr(text,int4) or
> >substr(text, int4, int4)
> >
> >For example,
> >
> >
> >% select substr('hi there',4,3);
> > substr
> >--------
> > the
> >(1 row)
> >
> >Morey Parang
> >ORNL
>
