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

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)
>