PROBLEM: Function does not exist

[Julian Legeny <julo@xxxxxxxxxxxxxxxxxx>]

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@xxxxxxxxxxxxxxxxxx


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@xxxxxxxxxxxxxx