FAQ
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

Search Discussions

  • Michael Fuhr at Jun 23, 2005 at 12:48 am

    On Thu, Jun 23, 2005 at 12:36:50AM +0200, Julian Legeny wrote:
    CREATE OR REPLACE FUNCTION insert_bf_filter(int4, int4, "varchar", "varchar", int4, int2, int2, int4, int2, int4) [-snip-]
    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.
    Follow the hint: use explicit type casts for the int2 arguments. Try this:

    select INTGR, TMSTP
    from INSERT_BF_FILTER(53, 1354, 'test_filter_name', 'test_filter_description',
    1, 0::int2, 0::int2, 10, 0::int2, 100);
  • Julian Legeny at Jun 23, 2005 at 1:23 am
    Hello Michael,

    yes, you have right. Solution is cast it to smallint.

    Thank you for your advice,
    with best regards,

    Julian Legeny

    Thursday, June 23, 2005, 2:48:17 AM, you wrote:

    MF> On Thu, Jun 23, 2005 at 12:36:50AM +0200, Julian Legeny wrote:
    CREATE OR REPLACE FUNCTION insert_bf_filter(int4, int4, "varchar",
    "varchar", int4, int2, int2, int4, int2, int4)
    MF> [-snip-]
    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.
    MF> Follow the hint: use explicit type casts for the int2 arguments. Try this:

    MF> select INTGR, TMSTP
    MF> from INSERT_BF_FILTER(53, 1354, 'test_filter_name', 'test_filter_description',
    MF> 1, 0::int2, 0::int2, 10, 0::int2, 100);
  • Tom Lane at Jun 23, 2005 at 1:38 am

    Michael Fuhr writes:
    On Thu, Jun 23, 2005 at 12:36:50AM +0200, Julian Legeny wrote:
    CREATE OR REPLACE FUNCTION insert_bf_filter(int4, int4, "varchar", "varchar", int4, int2, int2, int4, int2, int4)
    Follow the hint: use explicit type casts for the int2 arguments.
    Or just declare the function arguments as int4. I don't think there's
    any material performance advantage from using int2 here anyway.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJun 22, '05 at 10:36p
activeJun 23, '05 at 1:38a
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase