Thread: bytea question
hi all, in the pg_trigger table the tgargs column is defined as type "BYTEA". i can split this up in perl, once retrieved, but can't figure out how to "substring" it in sql. is there an SQL way to select pieces of a column of this type? any help is appreciated, mikeo
Try
substr(text,int4) or
substr(text, int4, int4)
For example,
% select substr('hi there',4,3);
substr
--------
the
(1 row)
Morey Parang
ORNL
On Tue, Aug 15, 2000 at 03:34:27PM -0400, mikeo wrote:
> hi all,
> in the pg_trigger table the tgargs column is defined
> as type "BYTEA". i can split this up in perl, once
> retrieved, but can't figure out how to "substring"
> it in sql. is there an SQL way to select pieces of
> a column of this type?
>
> any help is appreciated,
>
> mikeo
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
Well, I don't think you're going to be able to without resorting to
something other than straight sql (a c function would probably work).
You can get the value of a particular byte using get_byte(bytea,int)
but I can't think of a good way outside of some sort of function to
turn that into a split.
Also, get_byte elogs if the int is outside the range of octets on
the bytea. I would have expected it to work closer to substr on
text.
Stephan Szabo
sszabo@bigpanda.com
On Tue, 15 Aug 2000, 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
>
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)
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
>
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
>
thanks, i already have a perl script that splits based on
the backslash. guess i'll just stick with that. thanks for
you time and suggestions.
mikeo
At 06:06 PM 8/15/00 -0400, mjp@ornl.gov wrote:
>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)