Thread: Function overloading
I have tried to overload a function with the following signatures.
CREATE FUNCTION foo(int4, int4)
RETURNS NUMERIC AS '
CREATE FUNCTION foo(text, text)
RETURNS NUMERIC AS '
Can someone please tell me how to make these overloaded functions work
at the same time? I keep getting the following error.
psql:fdm:11: ERROR: Function 'foo(unknown, unknown)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts
The foo(text, text) works just fine alone when I do not compile/store the
foo(int4, int4) procedure in the database together with it. The
foo(int4, int4) procedure works regardless of whether I compile/store the
foo(text, text) procedure together with it in the database. The error
message seems to be complaining about the foo(text, text) procedure. I
have tried the following typecasts and format conversions to see if it
would accept it, but still with no success. Maybe I am casting the
types incorrectly. Can someone help me out?
select foo((text) 'testing', (text) 'testing');
select foo((varchar) 'testing', (varchar) 'testing');
select foo(text('testing'), text('testing'));
select foo(varchar('testing'), varchar('testing'));
... and others that did not work....
Here is the output of \df foo
List of functions
Result | Function | Arguments
---------+----------+------------
numeric | foo | int4 int4
numeric | foo | text text
(2 rows)
Additional info:
Operating System: Linux 2.2.16 #1 SMP i686
Postgres version: 7.0.2
Thanks.
Rynell Wesson
Rynell Wesson <rwesson@cs.utexas.edu> writes:
> I keep getting the following error.
> psql:fdm:11: ERROR: Function 'foo(unknown, unknown)' does not exist
That could only come out when you write
select foo('string', 'string')
and there is more than one possible candidate for foo(). The thing to
realize here is that Postgres does not assume that 'string' is a string.
Rather, it is a literal of as-yet-undetermined type. If the particular
foo() function can be identified uniquely, then the system can infer
what type the unknown-type literal should be converted to. Otherwise
it gets unhappy.
In the example you've given,
select foo(text('testing'), text('testing'));
ought to work fine to select the foo(text,text) function. It does work
when I try it. I wonder whether you are looking for the problem in the
wrong place. Is it possible that the error report is actually coming
from trying to evaluate the function body? What did you put in the
body, anyway?
BTW, Postgres 7.1 has been tweaked to be more willing to assume that
an unidentified-type literal is a string datatype, when it can't make
a unique decision otherwise. So in 7.1 you'll get the result you
expected from "select foo('string', 'string')". But the form with
the explicit casts should have worked anyway.
regards, tom lane
On Sat, 9 Dec 2000, Tom Lane wrote:
> Rynell Wesson <rwesson@cs.utexas.edu> writes:
> > I keep getting the following error.
>
> > psql:fdm:11: ERROR: Function 'foo(unknown, unknown)' does not exist
>
> That could only come out when you write
>
> select foo('string', 'string')
>
> and there is more than one possible candidate for foo(). The thing to
> realize here is that Postgres does not assume that 'string' is a string.
> Rather, it is a literal of as-yet-undetermined type. If the particular
> foo() function can be identified uniquely, then the system can infer
> what type the unknown-type literal should be converted to. Otherwise
> it gets unhappy.
>
> In the example you've given,
> select foo(text('testing'), text('testing'));
> ought to work fine to select the foo(text,text) function. It does work
> when I try it. I wonder whether you are looking for the problem in the
> wrong place. Is it possible that the error report is actually coming
> from trying to evaluate the function body? What did you put in the
> body, anyway?
Unless I missed something, the function bodies do not appear to be the
problem. They have both been tested seperately without any problems. It
seems as if there is a problem with the compiler's semantic analysis
phase. Here are the function bodies.
CREATE FUNCTION foo(text, text)
RETURNS NUMERIC AS '
DECLARE
p_qstn ALIAS FOR $1;
p_fname ALIAS FOR $2;
v_fid foq.fid%TYPE;
v_qid foq.qid%TYPE;
v_qnum foq.qnum%TYPE;
BEGIN
SELECT id INTO v_qid FROM qwert WHERE qstn = p_qstn;
SELECT id, nqs INTO v_fid, v_qnum FROM farm
WHERE name = p_fname;
v_qnum := v_qnum + 1;
UPDATE farm SET nqs = nqs + 1
WHERE name = p_fname;
INSERT INTO foq (fid, qid, qnum)
VALUES (v_fid, v_qid, v_qnum);
RETURN 0;
END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION foo(int4, int4)
RETURNS NUMERIC AS '
DECLARE
p_qid ALIAS FOR $1;
p_fid ALIAS FOR $2;
v_qnum foq.qnum%TYPE;
BEGIN
SELECT nqs INTO v_qnum FROM farm WHERE id = p_fid;
v_qnum := v_qnum + 1;
UPDATE farm SET nqs = nqs + 1
WHERE id = p_fid;
INSERT INTO foq (fid, qid, qnum)
VALUES (p_fid, p_qid, v_qnum);
RETURN 0;
END;'
LANGUAGE 'plpgsql';
>
> BTW, Postgres 7.1 has been tweaked to be more willing to assume that
> an unidentified-type literal is a string datatype, when it can't make
> a unique decision otherwise. So in 7.1 you'll get the result you
> expected from "select foo('string', 'string')". But the form with
> the explicit casts should have worked anyway.
>
> regards, tom lane
>
I've used a different casting syntax in SQL and PL/SQL (maybe you can try it):
SELECT foo('This is a string'::TEXT, 'This is a string'::TEXT);
SELECT foo('5'::INTEGER, '5'::INTEGER);
On Sunday 10 December 2000 01:51, Rynell Wesson wrote:
> On Sat, 9 Dec 2000, Tom Lane wrote:
> > Rynell Wesson <rwesson@cs.utexas.edu> writes:
> > > I keep getting the following error.
> > >
> > > psql:fdm:11: ERROR: Function 'foo(unknown, unknown)' does not exist
> >
> > That could only come out when you write
> >
> > select foo('string', 'string')
> >
> > and there is more than one possible candidate for foo(). The thing to
> > realize here is that Postgres does not assume that 'string' is a string.
> > Rather, it is a literal of as-yet-undetermined type. If the particular
> > foo() function can be identified uniquely, then the system can infer
> > what type the unknown-type literal should be converted to. Otherwise
> > it gets unhappy.
> >
> > In the example you've given,
> > select foo(text('testing'), text('testing'));
> > ought to work fine to select the foo(text,text) function. It does work
> > when I try it. I wonder whether you are looking for the problem in the
> > wrong place. Is it possible that the error report is actually coming
> > from trying to evaluate the function body? What did you put in the
> > body, anyway?
>
> Unless I missed something, the function bodies do not appear to be the
> problem. They have both been tested seperately without any problems. It
> seems as if there is a problem with the compiler's semantic analysis
> phase. Here are the function bodies.
>
> CREATE FUNCTION foo(text, text)
> RETURNS NUMERIC AS '
>
> DECLARE
>
> p_qstn ALIAS FOR $1;
> p_fname ALIAS FOR $2;
>
> v_fid foq.fid%TYPE;
> v_qid foq.qid%TYPE;
> v_qnum foq.qnum%TYPE;
>
> BEGIN
>
> SELECT id INTO v_qid FROM qwert WHERE qstn = p_qstn;
> SELECT id, nqs INTO v_fid, v_qnum FROM farm
> WHERE name = p_fname;
> v_qnum := v_qnum + 1;
>
> UPDATE farm SET nqs = nqs + 1
> WHERE name = p_fname;
>
> INSERT INTO foq (fid, qid, qnum)
> VALUES (v_fid, v_qid, v_qnum);
>
> RETURN 0;
>
> END;'
> LANGUAGE 'plpgsql';
>
>
> CREATE FUNCTION foo(int4, int4)
> RETURNS NUMERIC AS '
>
> DECLARE
>
> p_qid ALIAS FOR $1;
> p_fid ALIAS FOR $2;
>
> v_qnum foq.qnum%TYPE;
>
> BEGIN
>
> SELECT nqs INTO v_qnum FROM farm WHERE id = p_fid;
> v_qnum := v_qnum + 1;
>
> UPDATE farm SET nqs = nqs + 1
> WHERE id = p_fid;
>
> INSERT INTO foq (fid, qid, qnum)
> VALUES (p_fid, p_qid, v_qnum);
>
> RETURN 0;
>
> END;'
> LANGUAGE 'plpgsql';
>
> > BTW, Postgres 7.1 has been tweaked to be more willing to assume that
> > an unidentified-type literal is a string datatype, when it can't make
> > a unique decision otherwise. So in 7.1 you'll get the result you
> > expected from "select foo('string', 'string')". But the form with
> > the explicit casts should have worked anyway.
> >
> > regards, tom lane
--
-------- Robert B. Easter reaster@comptechnews.com ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------