Function that exists doesn't exist?

[Rob Johnston]

Howdy,

 

A little dumbfounded by this error message when trying to update a table in postgres 8.1, using the 8.1-404 JDBC3 driver. We're also running Java 1.5.0-05.

 

Java throws this exception:

org.postgresql.util.PSQLException: ERROR: function update_payable_account(integer, character varying, character varying, character varying, integer, numeric, integer) does not exist

at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1512)

at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1297)

at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:188)

at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:430)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:346)

at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:297)

 

Our function looks like this:

CREATE OR REPLACE FUNCTION update_payable_account(id int4, name "varchar", sup bpchar, cus bpchar, terms int2, del "numeric", astat int4)
  RETURNS void AS
$BODY$
begin
    update suppliers
    set
        supplier_name   = name,
        supplier_key  = sup,
        suppliers_customer_key  = cus,
        purchase_terms_id  = terms,
        delivery_charge  = del,
        account_status_id   = astat
    where supplier_id = id;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION update_payable_account(id int4, name "varchar", sup bpchar, cus bpchar, terms int2, del "numeric", astat int4) OWNER TO postgres;

 

And our calling code like this:

   sql = conn.prepareCall("{ call update_payable_account(?, ?, ?, ?, ?, ?, ?) }");
   sql.setInt(1, account.getAccountNumber());
   sql.setString(2, account.getName());
   sql.setString(3, account.getSupplierKey());
   sql.setString(4, account.getCustomerKey());
   sql.setInt(5, account.getPurchaseTermsID());
   sql.setBigDecimal(6, new BigDecimal(account.getDelivery()));   // this is a double
   sql.setInt(7, account.getAccountStatusID());

 

Cheers,

 

Rob Johnston.

[Tom Lane]

"Rob Johnston" <r...@aqq.com.au> writes:
> org.postgresql.util.PSQLException: ERROR: function =
> update_payable_account(integer, character varying, character varying, =

> character varying, integer, numeric, integer) does not exist

> Our function looks like this:
> CREATE OR REPLACE FUNCTION update_payable_account(id int4, name =
> "varchar", sup bpchar, cus bpchar, terms int2, del "numeric", astat =
> int4)

int2 is not integer, and bpchar is not varchar. There is an implicit
cast from varchar to bpchar, but not from int4 to int2.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend