Обсуждение: Functions just dont want to work! [hard]
Hi,
I'm having a problem at a code which worked already and now after installing the new postgresql version it doesn't work (i didnt check it actually for about 2-3 months but i didnt change anything in the meanwhile).
the problem occur only with functions which i created and the error which it gives me is (all of the functions get the same error) -
Warning: PostgreSQL query failed: ERROR: Function insert_new_field(integer, "unknown", "unknown") does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts.
now i echoed the sql line itself and its -
SELECT insert_new_field(24, '2', '2');
while the function code itself is -
CREATE
FUNCTION public.insert_new_field(int2, text, text) RETURNS text AS 'DECLARE
v_prod_id ALIAS FOR $1;
v_property ALIAS FOR $2;
v_value ALIAS FOR $3;
row_data fields%ROWTYPE;
BEGIN
FOR row_data IN
SELECT * FROM fields WHERE prod_id = v_prod_id LOOPIF row_data.property = v_property THEN
RAISE EXCEPTION ''Error: This property already exist at the DB'';
END
IF;END
LOOP;INSERT
INTO fields (prod_id, property, value)VALUES (v_prod_id, v_property, v_value);
RETURN ''Done'';
END;'
LANGUAGE 'plpgsql' VOLATILE;btw what's that volatile? and y it added to my function name "public." at her start name (i did tried to call the function with adding that name without any diffrent error)
can anyone help me here?
thx in advance
Yonatan
-----
"My friends worth gold to me, so i prefer to sell them and get rich!"
"My friends worth gold to me, so i prefer to sell them and get rich!"
On Wednesday 04 December 2002 11:32, Yonatan Ben-Nes wrote: > Hi, > > I'm having a problem at a code which worked already and now after > installing the new postgresql version it doesn't work (i didnt check it > actually for about 2-3 months but i didnt change anything in the > meanwhile). > > the problem occur only with functions which i created and the error which > it gives me is (all of the functions get the same error) - Warning: > PostgreSQL query failed: ERROR: Function insert_new_field(integer, > "unknown", "unknown") does not exist Unable to identify a function that > satisfies the given argument types You may need to add explicit typecasts. > > now i echoed the sql line itself and its - > SELECT insert_new_field(24, '2', '2'); > I think the clue to your solution is in the ERROR output. The database is telling you that the function does not exist. When I attempt to use the SQL statement in one of my databases ... I get the exact same error because I do not have that function in my DB either. When you upgraded versions ... did you re-create your database properly with output from a pg_dump? I always keep copies of my table definitions, triggers, and functions I have written in flat files so I can always re-create everything in the DB. My guess would be that if you added the function again, all would work properly. Andy
On Wed, Dec 04, 2002 at 08:32:34PM +0200, Yonatan Ben-Nes wrote: > Hi, > > I'm having a problem at a code which worked already and now after installing the new postgresql version it doesn't work(i didnt check it actually for about 2-3 months but i didnt change anything in the meanwhile). > > the problem occur only with functions which i created and the error which it gives me is (all of the functions get thesame error) - > Warning: PostgreSQL query failed: ERROR: Function insert_new_field(integer, "unknown", "unknown") does not exist Unableto identify a function that satisfies the given argument types You may need to add explicit typecasts. > > now i echoed the sql line itself and its - > SELECT insert_new_field(24, '2', '2'); > > while the function code itself is - > > CREATE FUNCTION public.insert_new_field(int2, text, text) RETURNS text AS ' Problem is that 24 is not an int2 -- it's an int4 (generic int type). For example: joel@joel=# create function hi(int2) returns bool as 'begin return true; end;' language 'plpgsql'; CREATE FUNCTION joel@joel=# select hi(42); ERROR: Function hi(integer) does not exist Unable to identify a function that satisfies the given argument types You may need to add explicit typecasts select hi(42::int2); works just fine. Either add a function that takes int4,tedt,text input, or explicitly cast your int4 to int2, as show above. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant
On Wed, Dec 04, 2002 at 10:08:06PM +0200, Yonatan Ben-Nes wrote: > thx joel thats solved it :P > > say i didnt saw at the updated list that they changed anything at the new > version that was supposed to cause it... > did i miss a line there ? :) You're right; it does work w/7.2 but not with 7.3. Not sure if that's an intended change in type coercion or not. Anyone want to speak up here?
> Problem is that 24 is not an int2 -- it's an int4 (generic int type). > For example: > Yes it is ... int2 is a two byte integer, meaning it has a range from -32768 to +32767. Unless I totally mistaken, 24 falls within that range. Am I missing something vital to this problem? What you have said seems incorrect to me. > joel@joel=# create function hi(int2) returns bool as 'begin return true; > end;' language 'plpgsql'; > CREATE FUNCTION > joel@joel=# select hi(42); > ERROR: Function hi(integer) does not exist > Unable to identify a function that satisfies the given argument types > You may need to add explicit typecasts > It works fine for me: test=# create function hi(int2) returns bool as 'begin return true; test'# end;' language 'plpgsql'; CREATE test=# select hi(42); hi ---- t (1 row) test=# What version are you using? Andy
On Wed, Dec 04, 2002 at 01:25:43PM -0700, Andrew J. Kopciuch wrote: > > Problem is that 24 is not an int2 -- it's an int4 (generic int type). > > For example: > > > > Yes it is ... int2 is a two byte integer, meaning it has a range from > -32768 to +32767. Unless I totally mistaken, 24 falls within that range. > > Am I missing something vital to this problem? What you have said seems > incorrect to me. Well, of course, you're right in a strict sense. But the constant 24 in PG is parsed as an int4 not an int2, and PG isn't coercing it automatically to an int2 for the function. > What version are you using? His original setup worked in 7.2 (and still does for my test case); the behavior has changed in 7.3 (as shown in my email). -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant
Joel Burton <joel@joelburton.com> writes: > But the constant 24 in PG is parsed as an int4 not an int2, and PG isn't > coercing it automatically to an int2 for the function. Yes. This is an intermediate state; eventually I'd like 24 to be parsed as an int2 and then implicitly up-converted if it's used in a context where int4 is needed. However we are not there yet --- I posted a message on pghackers a few weeks ago about the problems that occurred in an initial attempt to make that happen. It breaks a lot of cases that work at the moment :-( There are a number of tradeoffs to be made here --- it's difficult to find a workable compromise between being flexible and being too flexible (ie, having the parser fail because it can't decide which alternative to pick). See the many threads about implicit coercions in the pghackers archives if you want to contribute ideas. Right at the moment I'm toying with the notion of using assignment coercion not implicit coercion when there is only one possible candidate function (ie, the name and number of arguments uniquely identifies the function) --- but I'm not sure how much that will help. It might just confuse people even more. regards, tom lane