Re: LIKE pattern matching

Tom Lane wrote:
> >   ... 'hi%there' LIKE 'hi\%there' ESCAPE '\' ...
> > or of course you could specify another escape character. afaik there is
> > no default explicit escape character in SQL99.
> I thought the agreement was to assume default ESCAPE '\' (or really
> ESCAPE '\\', unless you are proposing to break ALL Postgres applications
> rather than just all the ones that use LIKE?).

No, my proposal *only* affects the internal workings of the LIKE support
code, *not* the other backslashing which happens at the parser. That is
another can of worms as you point out. But...

> 1. I do not think it's acceptable to drop the backslash-quoting behavior
> with no notice.

Not all quoting behavior, as noted above.

> 2. It's not clear to me that the SQL default of "no quote character" is
> superior to having a default quote character, and therefore I'd actually
> argue that we should NEVER go to 100% SQL-and-nothing-but semantics on
> this point.

For the LIKE constructs, this isn't true. And you point out something
interesting which I hadn't noticed: to get the backslash quoting
behavior which was implemented in the LIKE code you actually had to use
*two* backslashes. Yuck.

Anyway, the point is that the effects of this proposed change are
limited to internal LIKE behavior only, *and* will give us richer and
more consistant features. istm that this is to be preferred over some
"halfway there" implementation which isn't exactly backward compatible
and isn't completely standards compliant.

That said, it is trivial to clean up the internal code as I propose but
to *also* support the default backslash (not SQL9x compliant, but what
the heck ;) by simply passing the right parameter to the new "two
argument" like() support routines. That parameter could be set back to
NULL after the next release to get us back to SQL9x compliance.

Oh, and I seem to have not committed the new strings regression test
output, but will do so soon.
                    - Thomas