Re: BUG #15057: Issue with UNKNOW type when calling a PostgreSQL function from java code

On Mon, Feb 12, 2018 at 3:09 AM, Praveen Kumar <praveenkumar52028(at)gmail(dot)com>
wrote:

>
> In this case I don't have a specific function like
> *decode(integer,integer,text,text)* ,I just have
> *decode(anyelement,anyelement,text,text) *
>
> [image: Inline image 4]
>
>
> My question is how does this second case working without a specific
> function with required data types?
>
>
When faced with a function invocation:

SELECT func(int, int);

​A function signature of (anyelement, anyelement) will match.

When faced with a function invocation:

SELECT func(int, numeric);

A function signature of (anyelement, anyelement) will NOT match.

While anyelement can indeed be pretty much "any element" when multiple are
present in a function signature all of them are of the same "element".

If you only have, say:

(anyelement, anyelement, text)
and
(int, numeric, text)

then

SELECT func(int, numeric, unknown)

should match able to be matched to the (int, numeric, text) function
signature. In the following:

CREATE FUNCTION mixed_unknown(in1 int, in2 numeric, in3 text)
RETURNS text
AS $$ SELECT 'mixed_unknown'::text; $$ LANGUAGE SQL;

CREATE FUNCTION mixed_unknown(in1 anyelement, in2 anyelement, in3 text)
RETURNS text
AS $$ SELECT 'mixed_unknown'::text; $$ LANGUAGE SQL;

SELECT mixed_unknown(1, 1.00, 'text');
SELECT mixed_unknown(1, 1, 'text');

Both queries should, and in 9.6 at least do, succeed.

So the system is at least intelligent enough to know to omit anyelement
signatures when searching among overloaded functions in this type of
situation.

So, back to Andrew's point, if you want an explanation as to why PostgreSQL
is erroring out in your specific situation you will have to do the legwork
like above to generate a self-contained script with the minimum (or near to
it) variety of function signatures in place that cause your invocation
attempt to fail. IOW, don't bother showing us function signatures with
more or less than 4 arguments but make sure you include most or all of the
ones that do - or at least enough to provoke the error. I suspect that if
you add them one-at-a-time that when you see the one causing the error it
will be evident why PostgreSQL cannot make a decision.

David J.