Thread: converting E'C:\\something' to bytea
Hello,
Is there any way of casting (reinterpreting) a varchar/text field
containing arbitrary backslashes to bytea without making an escaped
copy of the varchar/text first? In the examples below I am using a
constant E'...' for clarity, the value normally comes from a
varchar/text column in a table but the end behaviour is the same.
E.g.:
1) SELECT E'C:\\something'::bytea
ERROR: invalid input syntax for type bytea
--> essentially like calling decode(); bad in this case because of
the naked backslash!
2) SELECT replace(E'C:\\something', E'\\', E'\\\\')::bytea
--> works OK, but bad performance-wise because needed to make an
escaped copy of the string which is inefficient
3) CREATE DOMAIN my_varlena AS text;
CREATE CAST (my_varlena AS bytea) WITHOUT FUNCTION;
SELECT E'C:\\something'::my_varlena::bytea
ERROR: invalid input syntax for type bytea
--> WHY?
Thanks,
V.
Vlad Romascanu wrote: > Hello, > > Is there any way of casting (reinterpreting) a varchar/text field > containing arbitrary backslashes to bytea without making an escaped > copy of the varchar/text first? In the examples below I am using a > constant E'...' for clarity, the value normally comes from a > varchar/text column in a table but the end behaviour is the same. > > E.g.: > > 1) SELECT E'C:\\something'::bytea > ERROR: invalid input syntax for type bytea > --> essentially like calling decode(); bad in this case because of > the naked backslash! > > 2) SELECT replace(E'C:\\something', E'\\', E'\\\\')::bytea > --> works OK, but bad performance-wise because needed to make an > escaped copy of the string which is inefficient > > 3) CREATE DOMAIN my_varlena AS text; > CREATE CAST (my_varlena AS bytea) WITHOUT FUNCTION; > SELECT E'C:\\something'::my_varlena::bytea > ERROR: invalid input syntax for type bytea > --> WHY? Well, the '\\' is being converted to '\' because of the single-quotes, and then bytea is saying it doesn't know how to process \something. It sounds like you want bytea but don't want the ability to use backslash escapes to input the bytea values. I am unsure how to accomplish that. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Hi, Bruce, Yes, I essentially want to reinterpret text as bytea without any conversion or actual backslash logic coming in the process, in the same way pg_convert_from internally reinterprets the bytea return value from pg_convert as text without any additional logic. I.e. given the text field 'C:\some\dir' (E'C:\\some\\dir') which is 11 codepoints long and contains a grand total of two backslashes, I want those two backslashes to map to two byte values 0x5c in a corresponding 11-byte long bytea. :) I tried to achieve this zero-logic via CREATE DOMAIN ... WITHOUT FUNCTION and casting via the domain, assuming the lot would then behave like the aforementioned trick in the pg_convert_from implementation, but it doesn't seem to work that way. :( V. On Wed, Mar 16, 2011 at 11:51 AM, Bruce Momjian <bruce@momjian.us> wrote: > Vlad Romascanu wrote: >> Hello, >> >> Is there any way of casting (reinterpreting) a varchar/text field >> containing arbitrary backslashes to bytea without making an escaped >> copy of the varchar/text first? In the examples below I am using a >> constant E'...' for clarity, the value normally comes from a >> varchar/text column in a table but the end behaviour is the same. >> >> E.g.: >> >> 1) SELECT E'C:\\something'::bytea >> ERROR: invalid input syntax for type bytea >> --> essentially like calling decode(); bad in this case because of >> the naked backslash! >> >> 2) SELECT replace(E'C:\\something', E'\\', E'\\\\')::bytea >> --> works OK, but bad performance-wise because needed to make an >> escaped copy of the string which is inefficient >> >> 3) CREATE DOMAIN my_varlena AS text; >> CREATE CAST (my_varlena AS bytea) WITHOUT FUNCTION; >> SELECT E'C:\\something'::my_varlena::bytea >> ERROR: invalid input syntax for type bytea >> --> WHY? > > Well, the '\\' is being converted to '\' because of the single-quotes, > and then bytea is saying it doesn't know how to process \something. It > sounds like you want bytea but don't want the ability to use backslash > escapes to input the bytea values. I am unsure how to accomplish that. > > -- > Bruce Momjian <bruce@momjian.us> http://momjian.us > EnterpriseDB http://enterprisedb.com > > + It's impossible for everything to be true. + >
Bruce Momjian <bruce@momjian.us> writes:
> Vlad Romascanu wrote:
>> Is there any way of casting (reinterpreting) a varchar/text field
>> containing arbitrary backslashes to bytea without making an escaped
>> copy of the varchar/text first?
> Well, the '\\' is being converted to '\' because of the single-quotes,
> and then bytea is saying it doesn't know how to process \something. It
> sounds like you want bytea but don't want the ability to use backslash
> escapes to input the bytea values. I am unsure how to accomplish that.
A really dangerous way is
CREATE CAST (text AS bytea) WITHOUT FUNCTION;
It's dangerous because it assumes more than it ought to about the
internal representation of the two types ... but for a one-shot
conversion I think it'd be all right.
regards, tom lane
Hi, Tom, Why does: CREATE CAST (text AS bytea) WITHOUT FUNCTION; SELECT E'C:\\something'::text::bytea; work as expected, but (with the original text->bytea cast in place): CREATE DOMAIN my_varlena AS text; CREATE CAST (my_varlena AS bytea) WITHOUT FUNCTION; SELECT E'C:\\something'::my_varlena::bytea; does not (i.e. appears to cast from the domain's base type) -- is this related to http://postgresql.1045698.n5.nabble.com/bug-non-working-casts-for-domain-td1944238.html , should it be documented? Thx, V. On Wed, Mar 16, 2011 at 12:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Bruce Momjian <bruce@momjian.us> writes: >> Vlad Romascanu wrote: >>> Is there any way of casting (reinterpreting) a varchar/text field >>> containing arbitrary backslashes to bytea without making an escaped >>> copy of the varchar/text first? > >> Well, the '\\' is being converted to '\' because of the single-quotes, >> and then bytea is saying it doesn't know how to process \something. It >> sounds like you want bytea but don't want the ability to use backslash >> escapes to input the bytea values. I am unsure how to accomplish that. > > A really dangerous way is > > CREATE CAST (text AS bytea) WITHOUT FUNCTION; > > It's dangerous because it assumes more than it ought to about the > internal representation of the two types ... but for a one-shot > conversion I think it'd be all right. > > regards, tom lane >
Vlad Romascanu <vromascanu@accurev.com> writes:
> Hi, Tom,
> Why does:
> CREATE CAST (text AS bytea) WITHOUT FUNCTION;
> SELECT E'C:\\something'::text::bytea;
> work as expected, but (with the original text->bytea cast in place):
> CREATE DOMAIN my_varlena AS text;
> CREATE CAST (my_varlena AS bytea) WITHOUT FUNCTION;
> SELECT E'C:\\something'::my_varlena::bytea;
> does not
Domains are not meant to support ad-hoc cast paths like that ---
generally, the parser smashes domains to their base types before even
looking into pg_cast. The reason for this is that the defined pathway
is source_domain -> source_base_type -> dest_base_type -> dest_domain
and allowing user-defined cast paths to short-circuit that would create
all kinds of uncertainty, in particular whether or not constraints on
a destination domain had been verified.
Possibly we ought to disallow CREATE CAST involving a domain, since
you're not the first person to think that he can impose special cast
rules by using a domain.
regards, tom lane
Dear all, please forgive me, but I am so impressed that Oleg, besides his splendid work on TSearch, gives us such unbelievable pictures: http://www.dailymail.co.uk/sciencetech/article-1366794/Rainbow-cloud-towers-Mount-Everest.html I stumbled about this completely by accident, and can't reject the reflex to post this here, wow... Please excuse & all the best, Nick