user defined function call problem after upgrade 7.2.3 -> 7.3.2
От | kevin@mtel.co.uk (kevin) |
---|---|
Тема | user defined function call problem after upgrade 7.2.3 -> 7.3.2 |
Дата | |
Msg-id | 6675f17.0305190441.1c9a559d@posting.google.com обсуждение исходный текст |
Список | pgsql-admin |
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;
В списке pgsql-admin по дате отправления:
Следующее
От: kevin@mtel.co.uk (kevin)Дата:
Сообщение: Re: user defined function call problem after upgrade 7.2.3 -> 7.3.2