Обсуждение: LIKE - bug or expected behaviour?

Hello,

we have user specific views with names that follow this pattern:

_table_username

Strangely the following select:

SELECT table_name FROM information_schema.views WHERE table_schema = current_schema() AND table_name LIKE
'\_%\_username';

does also match views with names like table_username.

This syntax does match as expected though:

SELECT table_name FROM information_schema.views WHERE table_schema = current_schema() AND table_name LIKE
E'\\_%\\_username';

Is this behaviour to be expected?

Thank you very much,

Peter


--
GRATIS für alle GMX-Mitglieder: Die maxdome Movie-FLAT!
Jetzt freischalten unter http://portal.gmx.net/de/go/maxdome01

"Jan-Peter Seifert" <Jan-Peter.Seifert@gmx.de> writes:
> Strangely the following select:

> SELECT table_name FROM information_schema.views WHERE table_schema = current_schema() AND table_name LIKE
'\_%\_username';

> does also match views with names like table_username.

Unless you have standard_conforming_strings turned on, those backslashes
are going to be eaten by the string-literal parser.  So the LIKE code
just sees '_%_username'.

            regards, tom lane

Hello,

Tom Lane wrote:

>> SELECT table_name FROM information_schema.views WHERE table_schema = current_schema() AND table_name LIKE
'\_%\_username';
>
>> does also match views with names like table_username.
>
> Unless you have standard_conforming_strings turned on, those backslashes
> are going to be eaten by the string-literal parser.  So the LIKE code
> just sees '_%_username'.

a very belated thank you very much for the explanation. So it matched
every view ending on the user's name ...
Makes me wonder whether this query was broken to begin with or did this
change with time / introduction of standard conforming strings?

Thank you very much,

Peter

Jan-Peter Seifert <Jan-Peter.Seifert@gmx.de> wrote:

> Makes me wonder whether this query was broken to begin with or did
> this change with time / introduction of standard conforming strings?

The behavior with standard_conforming_strings off should match the
behavior prior to the introduction of this GUC.

-Kevin