Re: Quoting fun

Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu> writes:
> I've been looking at SQL99 while reviewing a book, and stumbled across
> some (new to me) behavior for double-quoted identifiers. The SQL99 way
> to embed a double-quote into a quoted identifier is to put in two
> adjacent double-quotes (much like is done for embedding single-quotes
> into string literals in SQL9x).

I looked at doing that a while ago, not because I knew it was in SQL99
but just because it seemed like a nice idea. I backed off though when
I realized that there are a *lot* of places that will break. scan.l
and pg_dump are just the tip of the iceberg --- there are many other
places, and probably lots of applications, that assume printing "%s"
is sufficient to protect an identifier. Be prepared for a lot of
mop-up work if you want to press forward with this.

> Currently, pg_dump escapes this by embedding a backslash/double-quote
> pair,

pg_dump is mistaken --- as you say, the backend doesn't accept
backslashes in doublequoted idents. (Since there is no way to get a
doublequote into an ident currently, pg_dump's check is dead code,
which is why no one noticed it was broken.)

> String literals can contain escaped characters, which postgres removes
> early in the parsing stage. These escapes are re-inserted *every time
> the string is returned in a query*.

Au contraire, the backend never re-inserts escapes.

regards, tom lane