postgresqltypescastinguser-defined-functionspostgresql-11

Function not found in PostgreSQL

I have a user-defined function in PostgreSQL 11.2 created as follows. It basically inserts values to two different tables:

CREATE OR REPLACE FUNCTION public.insertTest(
IN ID1 integer, 
IN Value1 character varying,
IN Value2 character varying,
IN Value3 character varying,
IN Status character varying,
IN Active_Flag integer, 
IN Stuff1 smallint,
IN stuff2 smallint)
RETURNS void
LANGUAGE 'plpgsql'

AS $BODY$
BEGIN

Insert into TableA 
(TA_ID,
 TA_Value1, 
 TA_Value2,
 TA_Value3, 
 TA_Value4,
 TA_Time, 
 TA_Flag)
values 
(ID1,
 Value1, 
 Value2,
 Value3, 
 Status,
 now(), 
 1);

Insert into TableB
(TA_ID,
 TB_ID,      Confidence,     Sev_Rate, 
 Last_Update_Time,   TB_Flag)
values
(currval('tablea_t_id_seq'), --TableA has an auto-increment field
 Active_Flag,    Stuff1,     Stuff2,
 now(), 
 0);

END;
$BODY$;

Now when I try to execute this function, the following does not works:

SELECT * FROM public.insertTest (
550, 'Test_Value1', 
'Test_Value2', 'Test_Value3', 
'DEL', 55, 1, 1)

and throws this error:

ERROR:  function insertTest(integer, unknown, unknown, unknown, unknown, integer, integer, integer) does not exist
LINE 1: select insertTest(550,'Test_Value1', 'Test_...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

But the following works:

SELECT * FROM public.insertTest (
550::integer, 'Test_Value1'::character varying, 
'Test_Value2'::character varying, 'Test_Value3'::character varying, 
'DEL'::character varying, 55::integer, 1::smallint, 1::smallint);

Can someone tell me why the 1st execution of the function does not work?

Solution

  • ... why the 1st execution of the function does not work?

    The exact answer is: Function Type Resolution.

    The varchar columns are not the problem (unlike another answer suggests). String literals like 'Test_Value1' (with single quotes) are initially type unknown and there is an implicit conversion to varchar for that.

    The int2 columns at the end are the "problem" (or rather, the mismatched input for those). Numeric literals like 1 (without quotes!) are initially assumed to be type integer. And there is no implicit cast from integer (int4) to smallint (int2). See:

    SELECT castsource::regtype, casttarget::regtype, castcontext
FROM   pg_cast
WHERE  castsource = 'int'::regtype
AND    casttarget = 'int2'::regtype;

    Reveals castcontext = 'a'. The manual about castcontext:

    e means only as an explicit cast (using CAST or :: syntax). a means implicitly in assignment to a target column, as well as explicitly. i means implicitly in expressions, as well as the other cases

    With an explicit cast, the function call succeeds:

    SELECT * FROM pg_temp.insertTest (
550, 'Test_Value1', 
'Test_Value2', 'Test_Value3', 
'DEL', 55, int2 '1', int2 '1');

    Or even just:

    SELECT * FROM pg_temp.insertTest (
550, 'Test_Value1', 
'Test_Value2', 'Test_Value3', 
'DEL', 55,  '1', '1');

    Now, with added quotes, those are string literals, initially type unknown, and there is an implicit conversion to int2 for those.

    fiddle

    Closely related, with step-by-step explanation: