Thread
-
bytea question
mikeo <mikeo@spectrumtelecorp.com> — 2000-08-15T19:34:27Z
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
-
Re: bytea question
mjp@ornl.gov — 2000-08-15T20:38:56Z
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 -
Re: bytea question
mikeo <mikeo@spectrumtelecorp.com> — 2000-08-15T21:23:38Z
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 -
Re: bytea question
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-15T21:45:41Z
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 > -
Re: bytea question
mjp@ornl.gov — 2000-08-15T22:06:20Z
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 > -
Re: bytea question
mjp@ornl.gov — 2000-08-15T22:08:50Z
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 > -
Re: bytea question
mikeo <mikeo@spectrumtelecorp.com> — 2000-08-16T13:04:51Z
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)