Re: Function overloading - Mailing list pgsql-novice

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
>