Thread: PROBLEM: Function does not exist

Hello,

   I  have  problem that I can't to call function because postgres can't to find
this function with specified parameters and I receive following error:

Caused by: java.sql.SQLException: ERROR: function update_bf_domain(integer, character varying,
                                  character varying, timestamp with time zone) does not exist

Here is my definition:

Table definition:
-----------------
      CREATE TYPE type_int_timestamp AS (intgr INTEGER, tmstp TIMESTAMP);

      CREATE TABLE BF_DOMAIN
      (
         ID SERIAL,
         NAME VARCHAR(100) NOT NULL,
         DESCRIPTION VARCHAR(1024) NULL,
         CREATION_DATE TIMESTAMP NOT NULL,
         MODIFICATION_DATE TIMESTAMP NOT NULL,
         CONSTRAINT BF_DOM_UQ UNIQUE (NAME),
         CONSTRAINT BF_DOM_PK PRIMARY KEY (ID)
      );


Function for update:
--------------------
      CREATE OR REPLACE FUNCTION UPDATE_BF_DOMAIN
      (
         INTEGER,
         VARCHAR(100),
         VARCHAR(1024),
         TIMESTAMP
      ) RETURNS type_int_timestamp AS '
      DECLARE
         in_domain_id ALIAS FOR $1;
         in_name ALIAS FOR $2;
         in_description ALIAS FOR $3;
         in_modification_date ALIAS FOR $4;
         out_updated_count INTEGER;
         out_timestamp TIMESTAMP;
         output_result type_int_timestamp;
      BEGIN
         SELECT INTO out_timestamp now();
         UPDATE BF_DOMAIN SET NAME = in_name, DESCRIPTION = in_description,
                MODIFICATION_DATE = out_timestamp WHERE ID = in_domain_id
                AND MODIFICATION_DATE = in_modification_date;

         GET DIAGNOSTICS out_updated_count = ROW_COUNT;
         output_result.intgr := out_updated_count;
         output_result.tmstp := out_timestamp;
         RETURN output_result;
      END;
      ' LANGUAGE 'plpgsql';


========================================
I call update function (from java) as following:

      updateStatement = dbConnection.prepareStatement(
                        "select INTGR, TMSTP from UPDATE_BF_DOMAIN (?, ?, ?, ?)");
      updateStatement.setInt(1, data.getId());
      updateStatement.setString(2, data.getName());
      updateStatement.setString(3, data.getDescription());
      updateStatement.setTimestamp(4, data.getModificationTimestamp());

      rsResults = updateStatement.executeQuery();
      if (rsResults.next())
      {
         iUpdateCount = rsResults.getInt(1);
         tmTimestamp = rsResults.getTimestamp(2);
      }

========================================

Can you help me please what I doing wrong and how can I solve this problem?

   Thank you in advance,
   with best regards,

   Julian Legeny

mailto:juleni@livetrade.cz

juleni@livetrade.cz writes:
> Caused by: java.sql.SQLException: ERROR: function update_bf_domain(integer, character varying,
>                                   character varying, timestamp with time zone) does not exist

>       CREATE OR REPLACE FUNCTION UPDATE_BF_DOMAIN
>       (
>          INTEGER,
>          VARCHAR(100),
>          VARCHAR(1024),
>          TIMESTAMP
>       ) RETURNS type_int_timestamp AS '

"timestamp" and "timestamp with time zone" are two different types ...
you probably need to declare the function using the latter.

            regards, tom lane

juleni@livetrade.cz writes:

> Hello,
>
>    I have problem that I can't to call function because postgres
> can't to find this function with specified parameters and I receive
> following error:
>
> Caused by: java.sql.SQLException: ERROR: function
> update_bf_domain(integer, character varying, character varying,
> timestamp with time zone) does not exist

TIMESTAMP and TIMESTAMP WITH TIME ZONE are different types.  It looks
as if Java is passing the latter.

-Doug