Re: Bug #939: Function parameter of type int2 fail when called.

Paul Johnston (paulj(at)sonalysts(dot)com) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
Function parameter of type int2 fail when called.

Long Description
In version 7.2 we defined a bunch of functions with parameters of type int2. In version 7.2 this worked as advertised. We then upgraded to version 7.3.2. After upgrading we were not able to run any functions that had the int2 type as a parameter. When we tried to run one of those functions we got the following error message:

Number: -2147467259
Description: ERROR: Function generate_snapshot(integer, integer, integer) does not exist. Unable to identify a function that satisfies the given argument types. You may need to add explicit typecasts.

The function generate_snapshot takes an int8, int8, int2. We did try an explicit cast which did not help. When we changed the int2 parameter type to int4 the function worked as designed.

In the example code I have put a few function definitions that work and do not work to give some examples.

Sample Code
'
LANGUAGE plpgsql IMMUTABLE;

--
-- TOC entry 509 (OID 18479)
-- Name: works_example (bigint); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION works_example (bigint) RETURNS bigint
AS '--works_example
declare

begin
raise notice ''We are in works_example!!!'';
return 3;
end;

'
LANGUAGE plpgsql IMMUTABLE;

--
-- TOC entry 510 (OID 18480)
-- Name: works_two_example (bigint); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION works_two_example (bigint) RETURNS integer
AS '--works_two_example
declare

begin
raise notice ''We are in works_two_example!!!'';
return 2;
end;

'
LANGUAGE plpgsql IMMUTABLE;

--
-- TOC entry 511 (OID 18481)
-- Name: works_three_example (bigint, integer, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION works_three_example (bigint, integer, text) RETURNS integer
AS '--works_three_example
declare

begin
raise notice ''We are in works_three_example!!!'';
return 3;
end;

'
LANGUAGE plpgsql IMMUTABLE;

--
-- TOC entry 512 (OID 18482)
-- Name: broke_one_example (bigint, smallint); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION broke_one_example (bigint, smallint) RETURNS bigint
AS '--broke_one_example
declare

begin
raise notice ''We are in broke_one_example!!!'';
return 1;
end;

'
LANGUAGE plpgsql IMMUTABLE;

--
-- TOC entry 513 (OID 18484)
-- Name: works_four_example (bigint, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION works_four_example (bigint, text) RETURNS smallint
AS '--works_four_example
declare

begin
raise notice ''We are in works_four_example!!!'';
return 4;
end;
'
LANGUAGE plpgsql IMMUTABLE;

--
-- TOC entry 514 (OID 18485)
-- Name: broke_two_example (bigint, smallint, text); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION broke_two_example (bigint, smallint, text) RETURNS integer
AS '--broke_two_example
declare

begin
raise notice ''We are in broke_two_example!!!'';
return 2;
end;
'
LANGUAGE plpgsql IMMUTABLE;

--
-- TOC entry 515 (OID 18486)
-- Name: broke_three_example (smallint); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION broke_three_example (smallint) RETURNS void
AS '--broke_three_example
declare

begin
raise notice ''We are in broke_three_example!!!'';
end;

No file was uploaded with this report

pgsql-bugs(at)postgresql(dot)org writes:
> In version 7.2 we defined a bunch of functions with parameters of type int2. In version 7.2 this worked as advertised. We then upgraded to version 7.3.2. After upgrading we were not able to run any functions that had the int2 type as a parameter. When we tried to run one of those functions we got the following error message:

If you are calling the function with a simple literal constant, you need
to cast the constant to int2 explicitly; downconversion from int4 to
int2 is not automatic anymore.

regards, tom lane