Re: Boolean operators without commutators vs. ALL/ANY

Excerpts from Florian Pflug's message of vie jun 17 10:49:46 -0400 2011:
> On Jun17, 2011, at 16:20 , Alvaro Herrera wrote:
> > Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011:
> >> So? How does that reduce that risk of somebody writing "pattern ~ text"
> >> instead of "text ~ pattern"? Modifying your quote from above
> >> --------
> >> foo ~ 'bar' /* foo is the text column, bar is the regex */
> >> 'bar' ~ foo /* no complaint but it's wrong */
> >>
> >> How do I tell which is the regex here?
> >> --------
> >
> > The regex is always to the right of the operator.
>
> Which is something you have to remember... It's not in any
> way deducible from "foo ~ bar" alone.

Maybe, but the mnemonic rule seems quite a bit easier (to me anyway).
In my head I think of ~ as "matches", so "text matches regex", whereas
"regex matches text" doesn't make as much sense. (Hmm now that I see
it, maybe in english this is not so clear, but in spanish the difference
is pretty obvious).

> >> How is that worse than the situation with "=~" and "~="?
> >
> > With =~ it is to the right, with ~= it is to the left.
>
> It's always where the tilde is. Yeah, you have to remember that.
> Just as today you have to remember that the pattern goes on the
> right side.

Well, the mnemonic would be that ~ is still "text matches regex", while
~= is "the weird operator that goes the other way around", so it's still
pretty clear.

> > I have sometimes needed to look up which is which on ~ and ~~.
> > I assume that whichever way we go here, we're still going to have to
> > look up operator definitions in docs or online help. This kind of help
> > doesn't, err, help all that much:
> >
> > alvherre=# \doS ~
> >
> > Listado de operadores
> > Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción
> > ------------+--------+--------------+--------------+----------------+--------------------------------------------
> > ...
> > pg_catalog | ~ | text | text | boolean | matches regular expression, case-sensitive
> >
> > Note that there's no way to tell which is the regex here. It'd be a lot
> > better if the description was explicit about it.
>
> I'm all for it, let's change the description then! Shall I submit a patch?

Yes, please.

> > (Or, alternatively,
> > use a different data type for regexes than plain text ... but that has
> > been in the Todo list for years ...)
>
> I actually like that idea. Since we probably don't want a type for every
> kind of pattern we support (like, similar to, regexp), such a type wouldn't
> be much more than a synonym for text though. I personally don't have a
> problem with that, but I somehow feel there's gonna be quite some pushback...

Hmm, why? Maybe that's something we can discuss.

> Also, do we really want to end up with a large number of commutator
> pairs with totally unrelated names? I fear that this *will* seriously
> harm readability of SQL statements, and we'll regret it badly.

Hmm.

I guess this wouldn't be much of a problem if you could use ANY/ALL with
a function instead of an operator, c.f. map().

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support