Functions just dont want to work! [hard]

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

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

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

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