X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3B00152EB5 for ; Sat, 20 Aug 2005 13:00:33 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 95609-03 for ; Sat, 20 Aug 2005 16:00:23 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.201]) by svr1.postgresql.org (Postfix) with ESMTP id 5BE2352EC1 for ; Sat, 20 Aug 2005 13:00:18 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id z6so526893nzd for ; Sat, 20 Aug 2005 09:00:18 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=googlemail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=MrYshP2IL7AecXnVCFheqR7gXQplSJ4BJ8V07HzC6JVmGM692PbaA2W9NrPQR8+gjfi374j1a5V3to25E0ddcX2AyDkk61PNB1poLFzSkoeblm1/2lnOgtc19ymbswcV8Gm53j693O+CBNt1tJbIUrWLBbYxWFe+FMrfWJSZ540= Received: by 10.36.33.15 with SMTP id g15mr23691nzg; Sat, 20 Aug 2005 09:00:18 -0700 (PDT) Received: by 10.36.247.65 with HTTP; Sat, 20 Aug 2005 09:00:18 -0700 (PDT) Message-ID: Date: Sat, 20 Aug 2005 18:00:18 +0200 From: Moritz Bayer To: mark.dingee@cox.net Subject: Re: Problem with self-made plpgsql-function / casting Cc: pgsql-sql@postgresql.org In-Reply-To: <1124552738.4224.5.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_5573_31483143.1124553618249" References: <1124552738.4224.5.camel@localhost.localdomain> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.424 required=5 tests=[AWL=0.343, HTML_30_40=0.056, HTML_MESSAGE=0.001, RCVD_BY_IP=0.024] X-Spam-Level: X-Archive-Number: 200508/177 X-Sequence-Number: 22405 ------=_Part_5573_31483143.1124553618249 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Thanks for your reply, but your tip didn't solve my problem. I used your function call: select fc_editlanguage(0, 'German'::varchar, 'de'::varchar, 1); but still got an error: ERROR: function fc_editlanguage(integer, character varying, character=20 varying, integer) does not exist HINT: No function matches the given name and argument types. You may need t= o=20 add explicit type casts. At least, as you pointed out, the passed parameters are recognized as=20 character varying and not as unknown! So I guess I'm one step further, but haven't reached my goal. Do you have another hint for me? Best regards, Moritz=20 2005/8/20, Mark Dingee :=20 >=20 > Moritz, >=20 > 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: >=20 > Look at your function call. Does it look like this? >=20 > select fc_editlanguage(123, 'some name', 'some text value', 456) >=20 > What you need to do is be more specific with the types being passed. >=20 > Try this instead: >=20 > select fc_editlanguage(123, 'some name'::varchar, 'some text > value'::varchar, 456) >=20 > Best of luck, > Mark >=20 > 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 :=3D 0; > > id :=3D $1; > > varlanguage :=3D cast($2 as varchar(60)); > > bowsershortcut :=3D cast($3 as varchar(10)); > > active :=3D $4; > > if(id=3D0) then > > insertdate :=3D 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=3Dvarlanguage, > > la_browsershortcut=3Dbrowsershortcut, la_active=3Dactive > > WHERE la_id =3D 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 > > >=20 >=20 --=20 Lenscare AG - Europas gr=F6=DF= ter=20 Kontaktlinsenversand ------=_Part_5573_31483143.1124553618249 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Thanks for your reply,
 
but your tip  didn't solve my problem. I used your function = call:
 
select fc_editlanguage(0, 'German'::varchar, 'de'::varchar, 1);
but still got an error:
 
ERROR:  function fc_editlanguage(integer, character varying, char= acter varying, integer) does not exist
HINT:  No function matches t= he given name and argument types. You may need to add explicit type casts.<= br> 
At least, as you pointed out, the passed parameters are recognized as&= nbsp;character varying and not as unknown!
So I guess I'm one step further, but haven't reached my goal.
 
Do you have another hint for me?
Best regards,
Moritz 

 
2005/8/20, Mark Dingee <mark.dingee@cox.net>:
Moritz,

The issue is most= likely with the passed parameters themselves, not the
assignments withi= n the function.  When you call the function, be sure to
cast arguments that don't comply with your function definition. &n= bsp;For
example:

Look at your function call.  Does it l= ook like this?

select fc_editlanguage(123, 'some name', 'some text v= alue', 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,
&g= t;
> I'm a newbie to postgres and trying to produce my first function= s.
> Everything works fine as long as it takes numeric parameters, bu= t 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",<= br>> integer) does not exist
> HINT:  No function matches the given name and argument t= ypes. 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 "pub= lic"."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 insertdat= e date;
> DECLARE active smallint;
>
> DECLARE varreturn smallin= t;
>
> Begin
>      varretu= rn :=3D 0;
>      id :=3D $1;
>&n= bsp;     varlanguage :=3D cast($2 as varchar(60));=
>      bowsershortcut :=3D cast($3 as = varchar(10));
>      active :=3D $4;
> &n= bsp;    if(id=3D0) then
>    =      insertdate :=3D now();
>   &n= bsp;     INSERT INTO tbl_language (la_language, la_brow= sershortcut,
> la_insertdate, la_active)
>   &nb= sp;     VALUES
>         (varlanguage, brow= sershortcut, insertdate, active);
>     &nbs= p;else
>          U= PDATE tbl_language SET la_language=3Dvarlanguage,
> la_browsershortcu= t=3Dbrowsershortcut, la_active=3Dactive
>    &nbs= p;     WHERE la_id =3D id;
>      end if;
>  &nbs= p;   return varreturn;
> end;
> $body$
> L= ANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>>
> Greetings from Germany,
> Moritz
>
>
> PS: I'm using postgres 8.0 on a xp system
>
<= br>



--
<img src=3D"http://ad.zanox.com/ppv/?2510394C5= 69771607 " align=3D"bottom" width=3D"1" height=3D"= 1" border=3D"0" hspace=3D"1"><a href=3D"= ;http://ad.za= nox.com/ppc/?2510394C569771607T" >Lenscare AG - Europas gr=F6=DFter Kontaktlinsenversand</a>=20 ------=_Part_5573_31483143.1124553618249--