Thread: Problem with self-made plpgsql-function / casting

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;

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
>  

Moritz Bayer <moritz.bayer@googlemail.com> writes:
>  ERROR: function public.fc_editlanguage(integer, "unknown", "unknown", 
> integer) does not exist
>
> CREATE OR REPLACE FUNCTION "public"."fc_editlanguage" (id bigint, name 
> varchar, kuerzel varchar, active smallint) RETURNS smallint AS

The short answer to this is to avoid declaring function arguments as
"smallint".  When you call this as, say,
select fc_editlanguage(42, 'foo', 'bar', 1);

the "42" and the "1" are initially typed as integer constants.  There's
an implicit up-cast from integer to bigint, so the parser has no problem
matching the 42 to a bigint parameter, but the down-cast from integer to
smallint is not implicit.  With the function as written you'd have to
cast to smallint explicitly:
select fc_editlanguage(42, 'foo', 'bar', 1::smallint);

This is enough of a notational pain in the neck that it's easier just to
declare the argument as integer.
        regards, tom lane