Hi,
I believe there is a bug in the PostgreSQL JDBC driver. I have a
function "sales_tax (in subtotal money) RETURNS money" with DDL below.
I am trying to execute the function with a CallableStatement. I use the
SQL of :
{ ? = CALL "public"."sales_tax"(?) }
I register the parameters and then execute the statement, but it fails
with the error message :
ERROR: function public.sales_tax(double precision) does not exist
I trace the code down to the
org.postgresql.jdbc2.AbstractJdbc2Statement.modifyJdbcCall(String
p_sql). At the end of this function on line 2384-2410 (Pasted below) it
makes a conversion. It seems that the CallableStatement converts the
original SQL above to :
select * from "public"."sales_tax"(?, ?) as result
If you execute this statement with substituted variable a terminal
window into PG, the statement will fail. The syntax seems to be
incorrect. But the SQL will execute correctly if you execute :
select * from "public"."sales_tax"(?) as result
I am not sure what is the correct behavior, but this doesn't seem to be
correct as I can't execute a function with a return value.
Does anyone know if this is a bug or a known limitation? ... or if I am
doing something wrong?
thanks
-John
== PROCEDURE DDL ==============================================
CREATE OR REPLACE FUNCTION public.sales_tax (in subtotal money) RETURNS
money AS
$BODY$
BEGIN
RETURN subtotal * 0.06;
END;
$BODY$
LANGUAGE 'plpgsql'
GO
================================================================
== AbstractJdbc2Statement.modifyJdbcCall(String p_sql) ===============
if (connection.haveMinimumServerVersion("8.1") &&
((AbstractJdbc2Connection)connection).getProtocolVersion() == 3)
{
String s = p_sql.substring(startIndex, endIndex );
StringBuffer sb = new StringBuffer(s);
if ( outParmBeforeFunc )
{
// move the single out parameter into the function call
// so that it can be treated like all other parameters
boolean needComma=false;
// have to use String.indexOf for java 2
int opening = s.indexOf('(')+1;
int closing = s.indexOf(')');
for ( int j=opening; j< closing;j++ )
{
if ( !Character.isWhitespace(sb.charAt(j)) )
{
needComma = true;
break;
}
}
if ( needComma )
{
sb.insert(opening, "?,");
}
else
{
sb.insert(opening, "?");
}
}
return "select * from " + sb.toString() + " as result";
}
================================================================