Re: Problem with self-made plpgsql-function / casting - Mailing list pgsql-sql

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
>