Обсуждение: bytea hex input/output

Silly little question, but is there something to input/output hex
escaped data into a bytea, ala

CREATE TABLE a (lob BYTEA);
INSERT into a (lob) VALUES ('\x01\x02\x00\x03\x04');
INSERT into a (lob) VALUES ('\x01\x00\x02\x00\x03\x04');

It seems to work (doesn't error), but when selecting the data back out,
it is truncated at the first \x00, ala

SELECT lob FROM a;

  lob
----------
 \001\002
 \001

SELECT octet_length(lob) from a;

 octet_length
--------------
            2
            1

Why does this happen?  Also, can I get the output in similar hex form?

"Michael Artz" <mlartz@gmail.com> writes:
> Silly little question, but is there something to input/output hex
> escaped data into a bytea, ala

PQescapeBytea, perhaps?  The way you are doing it has multiple problems.

            regards, tom lane

Eh, I'll just convert it all to octal, I just thought that I could get away without any middleman.

What ig going on behind the scenes?  Does it first get converted to text and then on to bytea?  Would an explicit cast get around this (perhaps with associated UDF)?

-Mike

"Michael Artz" <mlartz@gmail.com> writes:
> What ig going on behind the scenes?  Does it first get converted to text and
> then on to bytea?

No, the \nnn escape is built into the lexer's syntax for a string
literal (see backend/parser/scan.l), and only after that does the string
get fed to bytea's input routine (or any other datatype's either).
In hindsight this was a horribly bad idea that we'll be paying through
the nose for, for some time to come :-(.  But we're stuck with it for
the moment.

            regards, tom lane

Tom Lane wrote:
> "Michael Artz" <mlartz@gmail.com> writes:
> > What ig going on behind the scenes?  Does it first get converted to text and
> > then on to bytea?
>
> No, the \nnn escape is built into the lexer's syntax for a string
> literal (see backend/parser/scan.l), and only after that does the string
> get fed to bytea's input routine (or any other datatype's either).
> In hindsight this was a horribly bad idea that we'll be paying through
> the nose for, for some time to come :-(.  But we're stuck with it for
> the moment.

Should we rethink this for SQL standard strings?

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> No, the \nnn escape is built into the lexer's syntax for a string
>> literal (see backend/parser/scan.l),

> Should we rethink this for SQL standard strings?

We already have: \ isn't an escape anymore when
standard_conforming_strings is true.

            regards, tom lane