Thread: Function that exists doesn't exist?

Function that exists doesn't exist?

From

"Rob Johnston"

Date:

11 February 2006, 15:22:54

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.

Re: Function that exists doesn't exist?

From

Tom Lane

Date:

11 February 2006, 15:55:46

"Rob Johnston" <rob@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