pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedEncoding bytea
3+ messages / 2 participants
[nested] [flat]
* Encoding bytea
@ 2004-03-03 13:12 Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Achilleus Mantzios @ 2004-03-03 13:12 UTC (permalink / raw)
To: pgsql-sql
Hi,
is there a way to encode a bytea in such a way that the resulting
text stream be readily available (\\ escaped for unprintable chars) for
usage in an insert statement?
None of base64,hex,escape options in encode() seem to produce
anything close.
This is meant to be used with generating insert statements in xml files
for remote processing.
I can always (in java) ResultSet.getString(bytea_col_idx),
get the result (ala psql) with one "\" trimmed out,
and process the output (by adding an extra "\" where needed) from there,
but i am not sure if the jdbc driver will be allowing this ... convinience
in the future.
Thanx.
--
-Achilleus
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Encoding bytea
@ 2004-03-03 22:59 Joe Conway <mail@joeconway.com>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 3+ messages in thread
From: Joe Conway @ 2004-03-03 22:59 UTC (permalink / raw)
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql
Achilleus Mantzios wrote:
> is there a way to encode a bytea in such a way that the resulting
> text stream be readily available (\\ escaped for unprintable chars) for
> usage in an insert statement?
>
> None of base64,hex,escape options in encode() seem to produce
> anything close.
>
> This is meant to be used with generating insert statements in xml files
> for remote processing.
Is this what you need?
create table t(f bytea);
insert into b values ('a\\003\\000\\001b');
create or replace function bytea2text(bytea) returns text as '
begin
return $1;
end;
' language plpgsql;
regression=# select 'insert into t values(' ||
quote_literal(bytea2text(f)) || ');' from t;
?column?
--------------------------------------------
insert into t values('a\\003\\000\\001b');
(1 row)
regression=# insert into t values('a\\003\\000\\001b');
INSERT 292656 1
HTH,
Joe
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Encoding bytea
@ 2004-03-04 07:54 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 3+ messages in thread
From: Achilleus Mantzios @ 2004-03-04 07:54 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: pgsql-sql
O kyrios Joe Conway egrapse stis Mar 3, 2004 :
> Achilleus Mantzios wrote:
>
> > is there a way to encode a bytea in such a way that the resulting
> > text stream be readily available (\\ escaped for unprintable chars) for
> > usage in an insert statement?
> >
> > None of base64,hex,escape options in encode() seem to produce
> > anything close.
> >
> > This is meant to be used with generating insert statements in xml files
> > for remote processing.
>
> Is this what you need?
Yes thanx. ( i always forget about the casting function trick)
>
> create table t(f bytea);
> insert into b values ('a\\003\\000\\001b');
>
> create or replace function bytea2text(bytea) returns text as '
> begin
> return $1;
> end;
> ' language plpgsql;
>
> regression=# select 'insert into t values(' ||
> quote_literal(bytea2text(f)) || ');' from t;
> ?column?
> --------------------------------------------
> insert into t values('a\\003\\000\\001b');
> (1 row)
>
> regression=# insert into t values('a\\003\\000\\001b');
> INSERT 292656 1
>
> HTH,
>
> Joe
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
--
-Achilleus
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-03-04 07:54 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-03 13:12 Encoding bytea Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-03-03 22:59 ` Joe Conway <mail@joeconway.com>
2004-03-04 07:54 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox