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
Show quoted text
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:
Show quoted text
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:
Show quoted text
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 typecaststig4=# 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\000what 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)
Show quoted text
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 typecaststig4=# 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\000what 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
Show quoted text
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 typecaststig4=# 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\000what 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:
Show quoted text
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)