Function not found in PostgreSQL

Question

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?

Erwin Brandstetter · Accepted Answer · 2023-11-01 03:35:27Z

... 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:

SparkFountain · Accepted Answer · 2019-08-19 12:26:54Z

1

As you can see from the error message, PostgreSQL expects that you call function insertTest(integer, unknown, unknown, unknown, unknown, integer, integer, integer). All parameters of type character varying are not regarded as such and therefore the inputs will be cast to unknown.

You'll find an adequate explanation in this Stackoverflow post (look at the comments). Fortunately, you already have a potential solution by providing type declarations explicitly, at least for your character varying parameters.

answered Aug 19, 2019 at 12:26

SparkFountain

2,2701 gold badge18 silver badges37 bronze badges

Collectives™ on Stack Overflow

Function not found in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related