user defined function call problem after upgrade 7.2.3 -> 7.3.2

From: kevin(at)mtel(dot)co(dot)uk (kevin)
To: pgsql-admin(at)postgresql(dot)org
Subject: user defined function call problem after upgrade 7.2.3 -> 7.3.2
Date: 2003-05-19 12:41:39
Message-ID: 6675f17.0305190441.1c9a559d@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Stefan Sturm 2003-05-19 15:16:16 Howto Setup a good PostgreSQL Server
Previous Message Együd Csaba 2003-05-18 08:16:28 Cygwin - Postgres won't start