Re: MySQL search query is not executing in Postgres DB

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> I do think that applying some kind of explicit flag to the function
> indicating whether it should allow implicit assignment
> casting/implicit casting to text/overloading/whatever is a possibly
> interesting alternative.

That idea seems possibly worth pursuing. The thing that I find scary
about the current proposal is that it applies to all functions (and
operators) willy-nilly, which seems to raise the risk of unexpected
side effects pretty high. If we could confine the behavioral change
to a relatively small number of functions for which there was consensus
that they should accept most anything, I'd feel better about it.

(Of course, we might then conclude that something close to the
quote_literal solution would work as well as a new function property.
But it's worth thinking about.)

> The fact that quote_literal() allows (by the expedient of
> overloading) implicit casts to text and that lpad() does not seems
> fairly random to me in hindsight; is there a general principle there
> that we'd all sign on to?

I don't find that random in the slightest. The entire purpose of
quote_literal is "manufacture a SQL-literal string representation of
this value", and that clearly might apply to data of any type. lpad()
is, first last and only, a textual operation. Somebody who thinks it
should apply directly to an integer is guilty of sloppy thinking at
best, or not even understanding what a data type is at worst.

regards, tom lane