Re: interpret bytea output as text / double encode()

On Fri, Jun 06, 2014 at 10:30:56AM -0400, Tom Lane wrote:
> It's possible that what you are looking for is a binary-equivalent
> cast from text to bytea, which you could create like this:

> # create cast (text as bytea) without function;

Hm. No, actually it does not help. But playing around with the cast
revealed to me another thing. If I double-encode "12345", I have:

| # select encode('\x5c7833313332333333343335', 'escape');
| encode
| ---------------
| \\x3132333435
| (1 row)

Now the intermediate result contains a '\\' which I just ignored as
it is the usual quoting for a '\'. *But* of course this prevents the
string from being correctly interpreted as a bytea-sequence, so the
solution for the problem is:

| # select encode(right(encode('\x5c7833313332333333343335', 'escape'), -1)::bytea, 'escape');
| encode
| --------
| 12345
| (1 row)

Kind of a hack, but this works on tables as well and will save me a
lot of trouble. Thank you for the inspiration!

Stefan