Обсуждение: PostgreSQL 9.0.3 cannot choose the best candidate function with type casts

Good day,

I have a query in which I'm using a function called fx_get_parcel() like this:
SELECT fx_get_parcel(cast('cash' AS varchar), cast(rs_i.d_depot AS varchar)) FROM...
Without the type casts, Postgres complains that I have to add them. When I add them, the query fails and I get this
hint:
HINT: Could not choose a best candidate function. You might need to add explicit type casts.

The function has two versions:
fx_get_parcel(character varying, character varying)
and
fx_get_parcel(character varying, character varying, integer)

Both are in plpgsql.
Any ideas on how I could make it choose the first version with two parameters?
I've tried to find it in the documentation but I must have missed it somewhere.

I'm running PostgreSQL 9.0.3 on 32-bit Windows XP.

Thanks in advance. :)

Peter

<slapo@centrum.sk> writes:
> I have a query in which I'm using a function called fx_get_parcel() like this:
> SELECT fx_get_parcel(cast('cash' AS varchar), cast(rs_i.d_depot AS varchar)) FROM...
> Without the type casts, Postgres complains that I have to add them.

Complains how exactly?

> When I add them, the query fails and I get this hint:
> HINT: Could not choose a best candidate function. You might need to add explicit type casts.

> The function has two versions:
> fx_get_parcel(character varying, character varying)
> and
> fx_get_parcel(character varying, character varying, integer)

I'll bet a good lunch that there are additional functions named
fx_get_parcel that you've forgotten are present.  Either that,
or the three-argument form has a default value specified for the
integer parameter, which makes the call simply ambiguous.

I wouldn't expect the literal-constant argument to need explicit casting
in any case.  If rs_i.d_depot is of a non-string datatype (integer for
instance), you probably would need an explicit cast here to make it into
a string.  However, you wouldn't get the above error message from that.

            regards, tom lane

> Od: "Tom Lane" <tgl@sss.pgh.pa.us>
> Komu: slapo@centrum.sk
> Dátum: 04.04.2011 17:13
> Predmet: Re: [NOVICE] PostgreSQL 9.0.3 cannot choose the best candidate function with type casts
>
> CC: pgsql-novice@postgresql.org
><slapo@centrum.sk> writes:
>> I have a query in which I'm using a function called fx_get_parcel() like this:
>> SELECT fx_get_parcel(cast('cash' AS varchar), cast(rs_i.d_depot AS varchar)) FROM...
>> Without the type casts, Postgres complains that I have to add them.
>
>Complains how exactly?

function fx_get_parcel(unknown, character varying) is not unique LINE 2: fx_get_parcel('cash', rs_i.d_depot) AS
parcel_numbe...^ HINT: Could not choose a best candidate function. You might need to add explicit type casts. 

>
>> When I add them, the query fails and I get this hint:
>> HINT: Could not choose a best candidate function. You might need to add explicit type casts.
>
>> The function has two versions:
>> fx_get_parcel(character varying, character varying)
>> and
>> fx_get_parcel(character varying, character varying, integer)
>
>I'll bet a good lunch that there are additional functions named
>fx_get_parcel that you've forgotten are present.  Either that,
>or the three-argument form has a default value specified for the
>integer parameter, which makes the call simply ambiguous.

>
>I wouldn't expect the literal-constant argument to need explicit casting
>in any case.  If rs_i.d_depot is of a non-string datatype (integer for
>instance), you probably would need an explicit cast here to make it into
>a string.  However, you wouldn't get the above error message from that.
>
>            regards, tom lane
>

rs_i.d_depot is of type character varying(4).
I owe you one then if I ever get to the US or if you know of a place with a delivery service that accepts PayPal.
The other function has a default value that I haven't noticed. I knew it was something silly I had overlooked.

Thanks,

Peter