Stefan Froehlich <postgresql@froehlich.priv.at> writes:
> ... Casting the result does not work either:
> # select encode(encode(column, 'escape')::bytea, 'escape')
> because the cast reverts the effect of the first encode(), so the result
> does not change.
Since there's no explicitly defined cast from text to bytea according
to pg_cast, what happens when you do this is that the textual result
of the inner encode() is fed to bytea's input function, which of course
is supposed to decode escaped data.
> If I take the result of the first encode() with copy/paste and put
> it through another encode() statement, everything is fine - but I
> have not found out a way how to automate this.
I'm a bit confused by this statement, because it sounds like what
you did manually is precisely to feed the text string to bytea's
input function. So I don't see quite what the difference is.
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;
However when I experiment with
# select encode(encode(column, 'escape')::bytea, 'escape')
after doing that, it doesn't seem like the results are very useful,
so I think I'm misunderstanding.
regards, tom lane