Why the error happen is clear to me, in the example is also easy to see that the 7th parameter is the problem.
But I'm searching a more general way to find easily which of the parameter is the problem.
Suppose you have a function with 30 parameters with mixed sort of types.
They only way I know right now is to compare the position, name and type one parameter after the other until I found the one that do not match.
A sort of brute force.
Is there any better way to do that?
Thanks
On Mon, Jan 16, 2023 at 5:21 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
On 1/16/23 08:17, Adrian Klaver wrote:
> On 1/16/23 08:04, arons wrote:
>> Dear All,
>> I'm facing a general problem and I'm looking the best, fastest, way
>> how to identify the problem and solve it.
>>
>> As example assume we have a function like that:
>>
>> CREATE OR REPLACE FUNCTION testBinding01 (
>>
>> p_in01 bigint,
>>
>> p_in02 bigint,
>>
>> p_in03 bigint,
>>
>> p_in04 bigint,
>>
>> p_in05 bigint,
>>
>> p_in06 bigint,
>>
>> p_text7 text
>>
>> ) RETURNS text
>>
>> LANGUAGE sql
>>
>> AS $$
>>
>> select 'ciao';
>>
>> $$;
>>
>>
>>
>> I can call the function in some of the variant below:
>>
>> select testBinding01(1,2,3,4,5,6,7);
>>
>> select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 =>
>> 4,p_in05 => 5,p_in06 => 6,p_text7 => 7);
>>
>> select testBinding01(p_in01 => 1,p_in02 => 2,p_in03 => 3,p_in04 =>
>> 4,p_in05 => 5,p_in06 => 6,p_text9 => 'some txt');
>>
>>
>> All of the above, produce the error:
>>
>> *No function matches the given name and argument types.*
>
> In psql what does:
>
> \df test*
>
> return for the function name.
>
> I'm going to guess it might be testBinding01, in other words mixed case.
>
> Have you tried?:
>
> select "testBinding01"(1,2,3,4,5,6,7);
Forget the above. Instead:
select testBinding01(1,2,3,4,5,6,7);
ERROR: function testbinding01(integer, integer, integer, integer,
integer, integer, integer) does not exist
LINE 1: select testBinding01(1,2,3,4,5,6,7);
select testBinding01(1,2,3,4,5,6,'7');
testbinding01
---------------
ciao
The complete error shows what the function is receiving, all integers
when it needs a text parameter for the last value.
>> *
>> *
>> *
>> *
>> *
>> *
>> My question is: how is the best way to identify the problem?
>>
>> Is a parameter name? is a parameter type? is the function name?
>>
>> An especially in case is a parameter type how is the easy way to
>> identify which parameter is causing the problem?
>>
>> In case a function has a lot of parameters (and in even worst case has
>> some overloading) going trough all parameters to check its type/name
>> costs a lot of time.
>>
>>
>> Thanks for any help
>>
>> Renzo
>>
>>
>>
>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
