Re: MySQL search query is not executing in Postgres DB

On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote:
> Here's yet another case where the current rules are thoroughly disagreeable.
>
> rhaas=# create or replace function z(smallint) returns smallint as
> $$select $1+1$$ language sql;
> ERROR: return type mismatch in function declared to return smallint
> DETAIL: Actual return type is integer.
> CONTEXT: SQL function "z"
>
> So cast the result from an integer to a smallint. What's the big deal?
>
> But, OK, I'll do it your way:
>
> rhaas=# create or replace function z(smallint) returns smallint as
> $$select $1+1::smallint$$ language sql;
> CREATE FUNCTION
> rhaas=# select z(1);
> ERROR: function z(integer) does not exist
> LINE 1: select z(1);
> ^
> HINT: No function matches the given name and argument types. You
> might need to add explicit type casts.
>
> Come on, really? Note that the above example works without casts if
> you use int *or* bigint *or* numeric, but not smallint. That could be
> fixed by causing sufficiently-small integers to lex as smallints, but
> if you think implicit datatype coercions are evil, you ought to be
> outraged by the fact that we are already going out of our way to blur
> the line between int, bigint, and numeric. We let people write 2.0 +
> 3 and get 5.0 - surely it's only a short step from there to human
> sacrifice, cats and dogs living together... mass hysteria! I mean,
> the whole reason for rejecting integer = text is that we aren't sure
> whether to coerce the text to an integer or the integer to a string,
> and it's better to throw an error than to guess. But in the case of
> 2.0 + 3, we feel 100% confident in predicting that the user will be
> happy to convert the integer to a numeric rather than the numeric to
> an integer, so no error. We do that because we know that the domain
> of numeric is a superset of the domain of integer, or in other words,
> we are using context clues to deduce what the user probably meant
> rather than forcing them to be explicit about it.

Is there any general interest in adjusting smallint casting?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +