Re: [SQL] Encoding bytea

[Joe Conway]

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
              http://archives.postgresql.org