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

Hi Pavel/Andrew,

*Case #1 :*

Even if I have a function ( Instead of having multiple functions with
polymorphic anyelement type) with required input parameters like below

*decode(numeric,integer,text,text)*

[image: Inline image 3]

It's not functioning as expected and showing below error,

ERROR: function decode(numeric, integer, unknown, unknown) does not exist
LINE 1: select decode(lotid,1,'Lot Id Found','Lot Id not found') fro...
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
********** Error **********

ERROR: function decode(numeric, integer, unknown, unknown) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Character: 8

*Case #2 :*

But when I try to run a select query like this

select decode(lotid::*integer*,1,'Lot Id Found','Lot Id not found') from
public.test_decode; - Used external casting

it is working fine and showing result.

[image: Inline image 2]

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?

Thanks,
Praveen.K

On Sat, Feb 10, 2018 at 5:18 PM, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "Praveen" == Praveen Kumar <praveenkumar52028(at)gmail(dot)com> writes:
>
> Praveen> [image: Inline image 2]
>
> In future please use text rather than screenshots, so that you can
> include the complete list rather than just a small part.
>
> Praveen> While calling one of these DECODE functions from my existing
> Praveen> select query,I am seeing an error saying
>
> Praveen> decode(numeric,integer,unknown,unknow) is not available
>
> Because you didn't include the complete list of function signatures in
> your message, we're left with the fact that the ones you _did_ show all
> start out with anyelement,anyelement. Since polymorphic function
> resolution requires all "anyelement" matches to be of the _same_ type,
> it's clear that none of these can match a call which has numeric,integer
> as the first two parameters.
>
> Even if you changed your second parameter to be numeric rather than
> integer, you'd then be faced with the fact that the list of signatures
> contains at least these:
>
> decode(anyelement,anyelement,bigint,bigint)
> decode(anyelement,anyelement,character,character)
> decode(anyelement,anyelement,date,date)
>
> So how would postgres be able to tell, given an argument list with types
> (numeric,numeric,unknown,unknown), which of these signatures to use?
> Obviously it would be ambiguous.
>
> --
> Andrew (irc:RhodiumToad)
>