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 B4F9952B79
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 20 Aug 2005 11:54:20 -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 14910-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 20 Aug 2005 14:54:19 +0000 (GMT)
Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.207])
	by svr1.postgresql.org (Postfix) with ESMTP id E0C7752A3C
	for <pgsql-sql@postgresql.org>; Sat, 20 Aug 2005 11:54:18 -0300 (ADT)
Received: by zproxy.gmail.com with SMTP id z6so523153nzd
	for <pgsql-sql@postgresql.org>; Sat, 20 Aug 2005 07:54:23 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=googlemail.com;
	h=received:message-id:date:from:to:subject:mime-version:content-type;
	b=K1bMQJkipfEBtg1gbgEnrVC/1YuR0270JQ9BN1jwplhsDfM8VI/L7/YZGvwyINlmCiyDjBi5mNwdGf3jpcR1JaleLMMQ+O9MsE1w3kxV5PJUpS22azrczxOHA7gR6XAZdsrPXwqYO8DCEciNF7GRZiyhWVWcPJq8T6Q/CZYkpjA=
Received: by 10.36.224.78 with SMTP id w78mr3188321nzg;
	Sat, 20 Aug 2005 07:54:22 -0700 (PDT)
Received: by 10.36.247.65 with HTTP; Sat, 20 Aug 2005 07:54:22 -0700 (PDT)
Message-ID: <c244500b0508200754193958c7@mail.gmail.com>
Date: Sat, 20 Aug 2005 16:54:22 +0200
From: Moritz Bayer <moritz.bayer@googlemail.com>
To: pgsql-sql@postgresql.org
Subject: Problem with self-made plpgsql-function / casting
Mime-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_5497_17123617.1124549662970"
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.081 required=5 tests=[HTML_30_40=0.056,
	HTML_MESSAGE=0.001, RCVD_BY_IP=0.024]
X-Spam-Level: 
X-Archive-Number: 200508/174
X-Sequence-Number: 22402

------=_Part_5497_17123617.1124549662970
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hello,
 I'm a newbie to postgres and trying to produce my first functions.=20
Everything works fine as long as it takes numeric parameters, but when I'm=
=20
trying to use varchar or text-parameters, it fails and throws the following=
=20
exception:
 ERROR: function public.fc_editlanguage(integer, "unknown", "unknown",=20
integer) does not exist
HINT: No function matches the given name and argument types. You may need t=
o=20
add explicit type casts.
 So I thought I would have to use the pgfunction cast(parameter as type)=20
before working with the parameter within the function. Still, the same=20
exception is thrown and I haven't got a clue how to solve this problem. Hop=
e=20
someone can help me out, here is my written function:
=20
CREATE OR REPLACE FUNCTION "public"."fc_editlanguage" (id bigint, name=20
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,=
=20
la_active)
VALUES
(varlanguage, browsershortcut, insertdate, active);
else
UPDATE tbl_language SET la_language=3Dvarlanguage,=20
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

------=_Part_5497_17123617.1124549662970
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

<div>Hello,</div>
<div>&nbsp;</div>
<div>I'm a newbie to postgres and trying to produce my first functions. Eve=
rything works fine as long as it takes numeric parameters, but when I'm try=
ing to use varchar or text-parameters, it fails and throws the following ex=
ception:
</div>
<div>&nbsp;</div>
<div>ERROR:&nbsp; function public.fc_editlanguage(integer, &quot;unknown&qu=
ot;, &quot;unknown&quot;, integer) does not exist<br>HINT:&nbsp; No functio=
n matches the given name and argument types. You may need to add explicit t=
ype casts.
</div>
<div>&nbsp;</div>
<div>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 ex=
ception 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:
</div>
<div>&nbsp;</div>
<p>CREATE OR REPLACE FUNCTION &quot;public&quot;.&quot;fc_editlanguage&quot=
; (id bigint, name varchar, kuerzel varchar, active smallint) RETURNS small=
int AS<br>$body$<br>DECLARE id bigint;<br>DECLARE varlanguage varchar(60);
<br>DECLARE browsershortcut varchar(10);<br>DECLARE insertdate date;<br>DEC=
LARE active smallint;</p>
<p>DECLARE varreturn smallint;</p>
<p>Begin<br>&nbsp;&nbsp;&nbsp;&nbsp; varreturn :=3D 0;<br>&nbsp;&nbsp;&nbsp=
;&nbsp; id :=3D $1;<br>&nbsp;&nbsp;&nbsp;&nbsp; varlanguage :=3D cast($2 as=
 varchar(60));<br>&nbsp;&nbsp;&nbsp;&nbsp; bowsershortcut :=3D cast($3 as v=
archar(10));<br>&nbsp;&nbsp;&nbsp;&nbsp; active :=3D $4;<br>&nbsp;&nbsp;&nb=
sp;&nbsp; if(id=3D0) then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ins=
ertdate :=3D now();
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INSERT INTO tbl_language (la=
_language, la_browsershortcut, la_insertdate, la_active)<br>&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp; VALUES<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&=
nbsp; (varlanguage, browsershortcut, insertdate, active);<br>&nbsp;&nbsp;&n=
bsp;&nbsp; else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; UPDATE =
tbl_language SET la_language=3Dvarlanguage, la_browsershortcut=3Dbrowsersho=
rtcut, la_active=3Dactive
<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHERE la_id =3D id;<br=
>&nbsp;&nbsp;&nbsp;&nbsp; end if;<br>&nbsp;&nbsp;&nbsp;&nbsp; return varret=
urn;<br>end;<br>$body$<br>LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT =
SECURITY INVOKER;</p>
<div><br>Greetings from Germany,</div>
<div>Moritz</div>
<div>&nbsp;</div>
<div>&nbsp;</div>
<div>PS: I'm using postgres 8.0 on a xp system<br>&nbsp;</div>

------=_Part_5497_17123617.1124549662970--