Re: user defined function call problem after upgrade 7.2.3 -> 7.3.2 - Mailing list pgsql-admin

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.