Re: BUG #15057: Issue with UNKNOW type when calling a PostgreSQL function from java code - Mailing list pgsql-bugs
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)
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.
In this case I don't have a specific function like decode(integer,integer,text,text) ,I just have decode(anyelement,anyelement,text,text)
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@tao11.riddles.org.uk> wrote:
>>>>> "Praveen" == Praveen Kumar <praveenkumar52028@gmail.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)
Attachment
pgsql-bugs by date: