"Thomas G. Lockhart" <lockhart@alumni.caltech.edu> writes:
> One "problem" with the backend is it escapes characters on both input
> _and_ output, which has always struck me as at least partially defeating
> the usefulness of escaping input.
I agree, that is a bug. Output data should not be escaped.
COPY IN/OUT data needs to be handled slightly differently from data seen
in SQL commands and SELECT results. In particular, COPY likes to
represent null values as \N, which can be confused with a valid string
value if you don't escape backslash.
It seems to me the rules should be:
* String data in SQL commands: quotes and backslashes must be escaped
with a backslash. This is pretty much forced by SQL string constant
syntax. (BTW, does the backend accept C-like syntax for control
characters in strings, eg, "\n" for newline or "\0" for a null
character? Should it?)
* Tuples returned by SELECT: no escaping, ever. Null values are
signaled separately so there's no need for \N to mean something special.
(If you have null characters in a string, you may have some difficulty
using C string functions on it, but that's your problem. The FE/BE
protocol for tuple data doesn't care, nor does libpq.)
* COPY IN/OUT: backslashes have to be escaped with a backslash (ie,
doubled) so that \N is unambiguously a NULL column. Also, it should
be possible to backslash a TAB to make it a data character rather than
a column separator. (Or perhaps better, support C-like \t, \n, etc.)
I have not looked at the code to see how close this is to reality,
but I think that's what we need to be doing.
regards, tom lane