PROBLEM: Function does not exist - Mailing list pgsql-general

Hello,

   I'm using postgres 8.0.3 under win xp. I have problem to call psql function.

   I have tables:
   1. BF_DOMAIN
   2. BF_USER
   3. BF_FILTER (this table uses FK from the previous two tables)

   and  function  INSERT_BF_FILTER that inserts data to the table BF_FILTER. But
when  I  call  this  function,  it  is  not  found  because incorrect parameters
"unknown" instead of varchar.

  Definition of tables and function:

CREATE TABLE bf_domain
(
  id serial NOT NULL,
  name varchar(100) NOT NULL,
  description varchar(1024),
  creation_date timestamp NOT NULL,
  modification_date timestamp NOT NULL,
  CONSTRAINT bf_dom_pk PRIMARY KEY (id),
  CONSTRAINT bf_dom_uq UNIQUE (name)
)
WITH OIDS;

----------------------------------------

CREATE TABLE bf_user
(
  id serial NOT NULL,
  domain_id int4 NOT NULL,
  first_name varchar(50) NOT NULL,
  last_name varchar(50) NOT NULL,
  phone varchar(20) NOT NULL,
  fax varchar(20) NOT NULL,
  address varchar(1024) NOT NULL,
  email varchar(50) NOT NULL,
  login_name varchar(30) NOT NULL,
  "password" varchar(50) NOT NULL,
  login_enabled int2 NOT NULL DEFAULT 0,
  guest_access_enabled int2 NOT NULL DEFAULT 0,
  super_user int2 NOT NULL DEFAULT 0,
  internal_user int2 NOT NULL DEFAULT 0,
  creation_date timestamp NOT NULL,
  modification_date timestamp NOT NULL,
  CONSTRAINT bf_usr_pk PRIMARY KEY (id),
  CONSTRAINT bf_usr_fk FOREIGN KEY (domain_id) REFERENCES bastatest.bf_domain (id) ON UPDATE NO ACTION ON DELETE
CASCADE,
  CONSTRAINT bf_usr_email_uq UNIQUE (email),
  CONSTRAINT bf_usr_login_uq UNIQUE (login_name)
)
WITH OIDS;

----------------------------------------

CREATE TABLE bf_filter
(
  id serial NOT NULL,
  domain_id int4 NOT NULL,
  user_id int4 NOT NULL,
  name varchar(50) NOT NULL,
  description varchar(1024),
  data_type int4 NOT NULL,
  shared int2 NOT NULL DEFAULT 0,
  match_all int2 NOT NULL DEFAULT 0,
  page_size int4 NOT NULL DEFAULT 10,
  order_desc int2 NOT NULL DEFAULT 0,
  order_column int4,
  creation_date timestamp NOT NULL,
  modification_date timestamp NOT NULL,
  CONSTRAINT bf_fltr_pk PRIMARY KEY (id),
  CONSTRAINT bf_fltr_dom_fk FOREIGN KEY (domain_id) REFERENCES bastatest.bf_domain (id) ON UPDATE NO ACTION ON DELETE
CASCADE,
  CONSTRAINT bf_fltr_usr_fk FOREIGN KEY (user_id) REFERENCES bastatest.bf_user (id) ON UPDATE NO ACTION ON DELETE
CASCADE
)
WITH OIDS;

----------------------------------------

CREATE TYPE type_int_timestamp AS
   (intgr int4,
    tmstp timestamp);

----------------------------------------

CREATE OR REPLACE FUNCTION insert_bf_filter(int4, int4, "varchar", "varchar", int4, int2, int2, int4, int2, int4)
  RETURNS type_int_timestamp AS
$BODY$
DECLARE
   IN_DOMAIN_ID ALIAS FOR $1;
   IN_USER_ID ALIAS FOR $2;
   IN_NAME ALIAS FOR $3;
   IN_DESCRIPTION ALIAS FOR $4;
   IN_DATA_TYPE ALIAS FOR $5;
   IN_SHARED ALIAS FOR $6;
   IN_MATCH_ALL ALIAS FOR $7;
   IN_PAGE_SIZE ALIAS FOR $8;
   IN_ORDER_DESC ALIAS FOR $9;
   IN_ORDER_COLUMN ALIAS FOR $10;
   out_key INTEGER;
   out_timestamp TIMESTAMP;
   output_result type_int_timestamp;
BEGIN
   SELECT INTO out_timestamp now();
   INSERT INTO BF_FILTER(DOMAIN_ID, USER_ID, NAME,
          DESCRIPTION, DATA_TYPE, SHARED, MATCH_ALL, PAGE_SIZE,
          ORDER_DESC, ORDER_COLUMN, CREATION_DATE, MODIFICATION_DATE)
   VALUES (IN_DOMAIN_ID, IN_USER_ID, IN_NAME, IN_DESCRIPTION, IN_DATA_TYPE,
           IN_SHARED, IN_MATCH_ALL, IN_PAGE_SIZE, IN_ORDER_DESC,
           IN_ORDER_COLUMN, out_timestamp, out_timestamp);
   out_key := CURRVAL('bf_filter_id_seq');
   output_result.intgr := out_key;
   output_result.tmstp := out_timestamp;
   RETURN output_result;
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

------------------------------------------------
------------------------------------------------

I call following SQL command from pgAdmin:
select INTGR, TMSTP from INSERT_BF_FILTER (53, 1354, 'test_filter_name', 'test_filter_description', 1, 0, 0, 10, 0,
100)

And it returns me following error:
ERROR:  function insert_bf_filter(integer, integer, "unknown", "unknown", integer, integer, integer, integer, integer,
integer)does not exist 
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.


Can you advice me what can be a problem?

--

Thank you for your answer,
best regards,

Julian

mailto:julo@opensubsystems.org