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
