pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with self-made plpgsql-function / casting
5+ messages / 3 participants
[nested] [flat]
* Problem with self-made plpgsql-function / casting
@ 2005-08-20 14:54 Moritz Bayer <moritz.bayer@googlemail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Moritz Bayer @ 2005-08-20 14:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with self-made plpgsql-function / casting
@ 2005-08-20 15:45 Mark Dingee <mark.dingee@cox.net>
parent: Moritz Bayer <moritz.bayer@googlemail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Mark Dingee @ 2005-08-20 15:45 UTC (permalink / raw)
To: Moritz Bayer <moritz.bayer@googlemail.com>; +Cc: 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
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with self-made plpgsql-function / casting
@ 2005-08-20 15:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Moritz Bayer <moritz.bayer@googlemail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-08-20 15:51 UTC (permalink / raw)
To: Moritz Bayer <moritz.bayer@googlemail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with self-made plpgsql-function / casting
@ 2005-08-20 16:00 Moritz Bayer <moritz.bayer@googlemail.com>
parent: Mark Dingee <mark.dingee@cox.net>
0 siblings, 0 replies; 5+ messages in thread
From: Moritz Bayer @ 2005-08-20 16:00 UTC (permalink / raw)
To: mark.dingee@cox.net; +Cc: pgsql-sql
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
varying, integer) does not exist
HINT: No function matches the given name and argument types. You may need to
add explicit type casts.
At least, as you pointed out, the passed parameters are recognized as
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 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
> >
>
>
--
<img src="http://ad.zanox.com/ppv/?2510394C569771607"; align="bottom"
width="1" height="1" border="0" hspace="1"><a href="
http://ad.zanox.com/ppc/?2510394C569771607T">Lenscare AG - Europas größter
Kontaktlinsenversand</a>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Problem with self-made plpgsql-function / casting
@ 2005-08-20 16:04 Moritz Bayer <moritz.bayer@googlemail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Moritz Bayer @ 2005-08-20 16:04 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
That's it!!!
Thanks a lot!
Moritz
PS: Hope some day I'll be the one to be an help for newbies! I'm working on
it!
2005/8/20, Tom Lane <tgl@sss.pgh.pa.us>:
>
> 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
>
--
<img src="http://ad.zanox.com/ppv/?2510394C569771607"; align="bottom"
width="1" height="1" border="0" hspace="1"><a href="
http://ad.zanox.com/ppc/?2510394C569771607T">Lenscare AG - Europas größter
Kontaktlinsenversand</a>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-08-20 16:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-20 14:54 Problem with self-made plpgsql-function / casting Moritz Bayer <moritz.bayer@googlemail.com>
2005-08-20 15:45 ` Mark Dingee <mark.dingee@cox.net>
2005-08-20 16:00 ` Moritz Bayer <moritz.bayer@googlemail.com>
2005-08-20 15:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-08-20 16:04 ` Moritz Bayer <moritz.bayer@googlemail.com>
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