Обсуждение: user defined function call problem after upgrade 7.2.3 -> 7.3.2

hi,

iv'e recently upgraded and all seemed fine with our app until a user
defined function was called today and we get the folowing error logged
on out web server.

SELECT updateordhdr('     5',01,'2003-05-20','O     5
12003-05-20','','','','','','',00,'O','O',00)
Gave: java.sql.SQLException: ERROR:  Function updateordhdr("unknown",
integer, "unknown", "unknown", "unknown", "unknown", "unknown",
"unknown", "unknown", "unknown", integer, "unknown", "unknown",
integer) does not exist
    Unable to identify a function that satisfies the given argument types
    You may need to add explicit typecasts

I've extracted the def from the db using pg_dump. I did not notice any
error messages while loading the functions (as part of a restore). Is
it complaining abouty the strings? I thought character and char
varying didn't need casting, and they certainly didn't before.

Any ideas?

--
-- TOC entry 152 (OID 196117)
-- Name: updateordhdr (character varying, smallint, date, character,
character varying, character varying, character varying, character
varying, character varying, character varying, smallint, character,
character, integer); Type: FUNCTION; Schema: public; Owner: root
--

CREATE FUNCTION updateordhdr (character varying, smallint, date,
character, character varying, character varying, character varying,
character varying, character varying, character varying, smallint,
character, character, integer) RETURNS integer
    AS ' DECLARE myaccount ALIAS FOR $1; mydelivery ALIAS FOR $2;
mythedate ALIAS FOR $3; mytheorder ALIAS FOR $4; mydeliverynote ALIAS
FOR $5; myinvoicenote ALIAS FOR $6; mygrnpod ALIAS FOR $7; mycomments
ALIAS FOR $8; mydcomments ALIAS FOR $9; myround ALIAS FOR $10;
mytempno ALIAS FOR $11; mystatus ALIAS FOR $12; myordertype ALIAS FOR
$13; mytransno ALIAS FOR $14; mycount int4; BEGIN SELECT COUNT(*) INTO
mycount FROM orderheader WHERE theorder=mytheorder; IF mycount=0 THEN
INSERT INTO orderheader

(account,delivery,thedate,theorder,deliverynote,invoicenote,grnpod,comments,dcomments,round,tempno,status,ordertype,transno)
VALUES
(myaccount,mydelivery,mythedate,mytheorder,mydeliverynote,myinvoicenote,mygrnpod,mycomments,mydcomments,myround,mytempno,mystatus,myordertype,mytransno);
ELSE UPDATE orderheader SET

account=myaccount,delivery=mydelivery,thedate=mythedate,theorder=mytheorder,deliverynote=mydeliverynote,invoicenote=myinvoicenote,grnpod=mygrnpod,comments=mycomments,dcomments=mydcomments,round=myround,tempno=mytempno,status=mystatus,ordertype=myordertype,transno=mytransno
WHERE theorder=mytheorder; END IF; RETURN mycount; END; '
    LANGUAGE plpgsql;


--
-- TOC entry 153 (OID 196117)
-- Name: updateordhdr (character varying, smallint, date, character,
character varying, character varying, character varying, character
varying, character varying, character varying, smallint, character,
character, integer); Type: ACL; Schema: public; Owner: root
--

REVOKE ALL ON FUNCTION updateordhdr (character varying, smallint,
date, character, character varying, character varying, character
varying, character varying, character varying, character varying,
smallint, character, character, integer) FROM PUBLIC;
GRANT ALL ON FUNCTION updateordhdr (character varying, smallint, date,
character, character varying, character varying, character varying,
character varying, character varying, character varying, smallint,
character, character, integer) TO PUBLIC;

kevin@mtel.co.uk (kevin) wrote in message news:<6675f17.0305190441.1c9a559d@posting.google.com>...
> hi,
>
> iv'e recently upgraded and all seemed fine with our app until a user
> defined function was called today and we get the folowing error logged
> on out web server.
>
> SELECT updateordhdr('     5',01,'2003-05-20','O     5
> 12003-05-20','','','','','','',00,'O','O',00)
> Gave: java.sql.SQLException: ERROR:  Function updateordhdr("unknown",
> integer, "unknown", "unknown", "unknown", "unknown", "unknown",
> "unknown", "unknown", "unknown", integer, "unknown", "unknown",
> integer) does not exist
>     Unable to identify a function that satisfies the given argument types
>     You may need to add explicit typecasts
>
> I've extracted the def from the db using pg_dump. I did not notice any
> error messages while loading the functions (as part of a restore). Is
> it complaining abouty the strings? I thought character and char
> varying didn't need casting, and they certainly didn't before.
>
> Any ideas?
>
> --
> -- TOC entry 152 (OID 196117)
> -- Name: updateordhdr (character varying, smallint, date, character,
> character varying, character varying, character varying, character
> varying, character varying, character varying, smallint, character,
> character, integer); Type: FUNCTION; Schema: public; Owner: root
> --
>
> CREATE FUNCTION updateordhdr (character varying, smallint, date,
> character, character varying, character varying, character varying,
> character varying, character varying, character varying, smallint,
> character, character, integer) RETURNS integer
>     AS ' DECLARE myaccount ALIAS FOR $1; mydelivery ALIAS FOR $2;
> mythedate ALIAS FOR $3; mytheorder ALIAS FOR $4; mydeliverynote ALIAS
> FOR $5; myinvoicenote ALIAS FOR $6; mygrnpod ALIAS FOR $7; mycomments
> ALIAS FOR $8; mydcomments ALIAS FOR $9; myround ALIAS FOR $10;
> mytempno ALIAS FOR $11; mystatus ALIAS FOR $12; myordertype ALIAS FOR
> $13; mytransno ALIAS FOR $14; mycount int4; BEGIN SELECT COUNT(*) INTO
> mycount FROM orderheader WHERE theorder=mytheorder; IF mycount=0 THEN
> INSERT INTO orderheader
>
(account,delivery,thedate,theorder,deliverynote,invoicenote,grnpod,comments,dcomments,round,tempno,status,ordertype,transno)
> VALUES
(myaccount,mydelivery,mythedate,mytheorder,mydeliverynote,myinvoicenote,mygrnpod,mycomments,mydcomments,myround,mytempno,mystatus,myordertype,mytransno);
> ELSE UPDATE orderheader SET
>
account=myaccount,delivery=mydelivery,thedate=mythedate,theorder=mytheorder,deliverynote=mydeliverynote,invoicenote=myinvoicenote,grnpod=mygrnpod,comments=mycomments,dcomments=mydcomments,round=myround,tempno=mytempno,status=mystatus,ordertype=myordertype,transno=mytransno
> WHERE theorder=mytheorder; END IF; RETURN mycount; END; '
>     LANGUAGE plpgsql;
>
>
> --
> -- TOC entry 153 (OID 196117)
> -- Name: updateordhdr (character varying, smallint, date, character,
> character varying, character varying, character varying, character
> varying, character varying, character varying, smallint, character,
> character, integer); Type: ACL; Schema: public; Owner: root
> --
>
> REVOKE ALL ON FUNCTION updateordhdr (character varying, smallint,
> date, character, character varying, character varying, character
> varying, character varying, character varying, character varying,
> smallint, character, character, integer) FROM PUBLIC;
> GRANT ALL ON FUNCTION updateordhdr (character varying, smallint, date,
> character, character varying, character varying, character varying,
> character varying, character varying, character varying, smallint,
> character, character, integer) TO PUBLIC;


the problem was the smallint fields. these are not auto converted and
the
function call has to be recoded to be
select fn(value::smallint, ...)

now it works fine. I'll add more updates if i find more non string
related update problems (these are covered by other posts).

Kev.

from what you copied and pasted, the 4th parameter is not a single character
as in the definition.

As an aside, I have found user defined functions to lead to slower total
script execution times in my php code.  Sure it reduces my code size, but
that doesn't translate directly to speed.  Something like this might be
better off as separate sql statements unless you need it for
portability, especially with the number of variables being passed.  For
example, if mycount != 0 then you have (wastefully) passed the function a
bunch of data that won't be used at all.

----- Original Message -----
From: "kevin" <kevin@mtel.co.uk>
To: <pgsql-admin@postgresql.org>
Sent: Monday, May 19, 2003 5:41 AM
Subject: [ADMIN] user defined function call problem after upgrade 7.2.3 ->
7.3.2


> hi,
>
> iv'e recently upgraded and all seemed fine with our app until a user
> defined function was called today and we get the folowing error logged
> on out web server.
>
> SELECT updateordhdr('     5',01,'2003-05-20','O     5
> 12003-05-20','','','','','','',00,'O','O',00)
> Gave: java.sql.SQLException: ERROR:  Function updateordhdr("unknown",
> integer, "unknown", "unknown", "unknown", "unknown", "unknown",
> "unknown", "unknown", "unknown", integer, "unknown", "unknown",
> integer) does not exist
> Unable to identify a function that satisfies the given argument types
> You may need to add explicit typecasts
>
> I've extracted the def from the db using pg_dump. I did not notice any
> error messages while loading the functions (as part of a restore). Is
> it complaining abouty the strings? I thought character and char
> varying didn't need casting, and they certainly didn't before.
>
> Any ideas?
>
> --
> -- TOC entry 152 (OID 196117)
> -- Name: updateordhdr (character varying, smallint, date, character,
> character varying, character varying, character varying, character
> varying, character varying, character varying, smallint, character,
> character, integer); Type: FUNCTION; Schema: public; Owner: root
> --
>
> CREATE FUNCTION updateordhdr (character varying, smallint, date,
> character, character varying, character varying, character varying,
> character varying, character varying, character varying, smallint,
> character, character, integer) RETURNS integer
>     AS ' DECLARE myaccount ALIAS FOR $1; mydelivery ALIAS FOR $2;
> mythedate ALIAS FOR $3; mytheorder ALIAS FOR $4; mydeliverynote ALIAS
> FOR $5; myinvoicenote ALIAS FOR $6; mygrnpod ALIAS FOR $7; mycomments
> ALIAS FOR $8; mydcomments ALIAS FOR $9; myround ALIAS FOR $10;
> mytempno ALIAS FOR $11; mystatus ALIAS FOR $12; myordertype ALIAS FOR
> $13; mytransno ALIAS FOR $14; mycount int4; BEGIN SELECT COUNT(*) INTO
> mycount FROM orderheader WHERE theorder=mytheorder; IF mycount=0 THEN
> INSERT INTO orderheader
>
(account,delivery,thedate,theorder,deliverynote,invoicenote,grnpod,comments,
dcomments,round,tempno,status,ordertype,transno)
> VALUES
(myaccount,mydelivery,mythedate,mytheorder,mydeliverynote,myinvoicenote,mygr
npod,mycomments,mydcomments,myround,mytempno,mystatus,myordertype,mytransno)
;
> ELSE UPDATE orderheader SET
>
account=myaccount,delivery=mydelivery,thedate=mythedate,theorder=mytheorder,
deliverynote=mydeliverynote,invoicenote=myinvoicenote,grnpod=mygrnpod,commen
ts=mycomments,dcomments=mydcomments,round=myround,tempno=mytempno,status=mys
tatus,ordertype=myordertype,transno=mytransno
> WHERE theorder=mytheorder; END IF; RETURN mycount; END; '
>     LANGUAGE plpgsql;
>
>
> --
> -- TOC entry 153 (OID 196117)
> -- Name: updateordhdr (character varying, smallint, date, character,
> character varying, character varying, character varying, character
> varying, character varying, character varying, smallint, character,
> character, integer); Type: ACL; Schema: public; Owner: root
> --
>
> REVOKE ALL ON FUNCTION updateordhdr (character varying, smallint,
> date, character, character varying, character varying, character
> varying, character varying, character varying, character varying,
> smallint, character, character, integer) FROM PUBLIC;
> GRANT ALL ON FUNCTION updateordhdr (character varying, smallint, date,
> character, character varying, character varying, character varying,
> character varying, character varying, character varying, smallint,
> character, character, integer) TO PUBLIC;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>