Re: Exception while executing function with CallableStatement

On Sun, 7 Mar 2004, Philip A. Chapman wrote:

> org.postgresql.util.PSQLException: ERROR: function instimelog(integer,
> integer, integer, "unknown", integer, integer, "unknown", "unknown")
> does not exist
>
> Since I'm setting the type using Types.INTEGER and Types.VARCHAR, I do
> not know why the exception is listing the types for the eight and nineth
> fields as "unknown".  I would appreciate any help.
>

This is indeed a bug, but in your case the error is because the function
does not exist.  There are two possible problems that I saw in my testing:

I created three functions

CREATE FUNCTION nullfunc(a timestamptz) RETURNS timestamptz AS
    'BEGIN RETURN $1; END;' LANGUAGE plpgsql
CREATE FUNCTION nullfunc(a int) RETURNS int AS
    'BEGIN RETURN $1; END;' LANGUAGE plpgsql
CREATE FUNCTION nullfunc(a text) RETURNS text AS
    'BEGIN RETURN $1; END;' LANGUAGE plpgsql

and java code that tries to call the int version:

CallableStatement cs = conn.prepareCall("{? = call nullfunc(?)}");
cs.registerOutParameter(1, Types.INTEGER);
cs.setNull(2, Types.INTEGER);
cs.execute();
int result = cs.getInt(1);

This resulted in the text version being called even though you would
expect the int version to be called, resulting in:

    A CallableStatement Function was executed and the return was of
    type (java.sql.Types=12) however type=java.sql.Types=4 was
    registered.

After deleting the text function the backend couldn't determine which
function to call and I get:

    ERROR: function nullfunc("unknown") is not unique

At the moment no casting is done for any function arguments.  We could do
something like the server side prepared statement's code in
AbstractJdbc1Statement.transformToServerPrepare for CallableStatements as
well.  Would we want that for all arguments or just for null ones?  Adding
the casts for non-null parameters would allow the selection between two
functions like f(int4) and f(int2), but could likely cause other
problems/unexpected behavior.

Kris Jurka