From: Mark Dingee <mark.dingee@cox.net>
To: Moritz Bayer <moritz.bayer@googlemail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem with self-made plpgsql-function / casting
Date: Sat, 20 Aug 2005 11:45:38 -0400
Message-ID: <1124552738.4224.5.camel@localhost.localdomain> (raw)
In-Reply-To: <c244500b0508200754193958c7@mail.gmail.com>
References: <c244500b0508200754193958c7@mail.gmail.com>
Moritz,
The issue is most likely with the passed parameters themselves, not the
assignments within the function. When you call the function, be sure to
cast arguments that don't comply with your function definition. For
example:
Look at your function call. Does it look like this?
select fc_editlanguage(123, 'some name', 'some text value', 456)
What you need to do is be more specific with the types being passed.
Try this instead:
select fc_editlanguage(123, 'some name'::varchar, 'some text
value'::varchar, 456)
Best of luck,
Mark
On Sat, 2005-08-20 at 16:54 +0200, Moritz Bayer wrote:
> Hello,
>
> I'm a newbie to postgres and trying to produce my first functions.
> Everything works fine as long as it takes numeric parameters, but when
> I'm trying to use varchar or text-parameters, it fails and throws the
> following exception:
>
> ERROR: function public.fc_editlanguage(integer, "unknown", "unknown",
> integer) does not exist
> HINT: No function matches the given name and argument types. You may
> need to add explicit type casts.
>
> So I thought I would have to use the pgfunction cast(parameter as
> type) before working with the parameter within the function. Still,
> the same exception is thrown and I haven't got a clue how to solve
> this problem. Hope someone can help me out, here is my written
> function:
>
>
> CREATE OR REPLACE FUNCTION "public"."fc_editlanguage" (id bigint, name
> varchar, kuerzel varchar, active smallint) RETURNS smallint AS
> $body$
> DECLARE id bigint;
> DECLARE varlanguage varchar(60);
> DECLARE browsershortcut varchar(10);
> DECLARE insertdate date;
> DECLARE active smallint;
>
> DECLARE varreturn smallint;
>
> Begin
> varreturn := 0;
> id := $1;
> varlanguage := cast($2 as varchar(60));
> bowsershortcut := cast($3 as varchar(10));
> active := $4;
> if(id=0) then
> insertdate := now();
> INSERT INTO tbl_language (la_language, la_browsershortcut,
> la_insertdate, la_active)
> VALUES
> (varlanguage, browsershortcut, insertdate, active);
> else
> UPDATE tbl_language SET la_language=varlanguage,
> la_browsershortcut=browsershortcut, la_active=active
> WHERE la_id = id;
> end if;
> return varreturn;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
>
> Greetings from Germany,
> Moritz
>
>
> PS: I'm using postgres 8.0 on a xp system
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mark.dingee@cox.net, moritz.bayer@googlemail.com
Subject: Re: Problem with self-made plpgsql-function / casting
In-Reply-To: <1124552738.4224.5.camel@localhost.localdomain>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox