java PostgreSQL-没有与给定名称和参数类型匹配的函数。您可能需要添加显式类型转换

Question

我在PostgreSQL中有一个存储过程，如下所示：

CREATE OR REPLACE FUNCTION insert_orderline(order_id integer, prod_id integer, prodname text, rate numeric, ordered_qty numeric, dispatched_qty numeric, balance_qty numeric, photonum text, remarks text, create_station_id integer, create_stationtype text, create_time text, create_user_id integer, tran_time text, tran_user_id integer)
  RETURNS void AS
$BODY$
   INSERT INTO bakingfactory.orderline
     (orderline_id, order_id, prod_id, prodname, rate, ordered_qty, 
       dispatched_qty, balance_qty, photonum, remarks, create_station_id, 
       create_stationtype, create_time, create_user_id, tran_time, tran_user_id)
   values 
     (default,order_id, prod_id, prodname, rate, ordered_qty, 
       dispatched_qty, balance_qty, photonum, remarks, create_station_id, 
       create_stationtype, create_time, create_user_id, tran_time, tran_user_id) 
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;
ALTER FUNCTION insert_orderline(integer, integer, text, numeric, numeric, numeric, numeric, text, text, integer, text, text, integer, text, integer)
  OWNER TO postgres;

CallableStatement cstorderline = conn.prepareCall("{call insert_orderline(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                            cstorderline.setInt(1, lastinsertid);
                            cstorderline.setInt(2, prodid);
                            cstorderline.setString(3, proname);
                            cstorderline.setDouble(4, rate);
                            cstorderline.setDouble(5, ordered_qty);
                            cstorderline.setDouble(6, dispatched_qty);
                            cstorderline.setDouble(7, balanced_qty);
                            cstorderline.setString(8, photonum);
                            cstorderline.setString(9, notes);
                            cstorderline.setInt(10, create_station_id);
                            cstorderline.setString(11, create_station_type);
                            cstorderline.setString(12, timewithmilsec);
                            cstorderline.setInt(13, create_user_id);
                            cstorderline.setString(14, timewithmilsec);
                            cstorderline.setInt(15, trans_user_id); 
                            cstorderline.executeUpdate();

org.postgresql.util.PSQLException: ERROR: function bakingfactory.insert_orderline(integer, integer, character varying, double precision, double precision, double precision, double precision, character varying, character varying, integer, character varying, character varying, integer, character varying, integer) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 15

我的数据库中有带精度值的numeric类型。对于numeric，我只需要使用double。这条路对吗？

Answer 1

对于您的错误有多种解释。

第一个解释

您已经创建了一个函数insert_orderline，但Java代码希望该函数包含在BakingFactory模式中（您可能已经将BakingFactory设置为Java应用程序的连接用户的默认模式）。

CREATE OR REPLACE FUNCTION bakingfactory.insert_orderline

而不是

CREATE OR REPLACE FUNCTION insert_orderline

第二种解释

应该在函数定义中使用双精度而不是数值数据类型。