postgresql - 在 PostgreSQL 中找不到函数

Question

我在 PostgreSQL 11.2 中创建了一个用户定义函数，如下所示。它基本上将值插入到两个不同的表中:

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$;

现在，当我尝试执行此功能时，以下内容不起作用:

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

并抛出这个错误:

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.

但以下工作:

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);

有人能告诉我为什么第一次执行该函数不起作用吗？

Answer 1

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

确切答案是:Function Type Resolution .

varchar 列不是问题(与另一个答案所暗示的不同)。 String literals(带单引​​号)最初是 unknown 类型，为此隐式转换为 varchar。

末尾的 int2 列是“问题”(或者更确切地说，是那些不匹配的输入)。 numeric literals 1(不带引号!)最初假定为 integer 类型。并且没有从 integer (int4) 到 smallint (int2) 的隐式转换 ).见:

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

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

通过显式转换，函数调用成功:

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

甚至只是:

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

现在，添加引号后，这些是字符串文字，最初类型为unknown，并且隐式转换为int2。

db<> fiddle here

密切相关，并有逐步解释:

• No function matches the given name and argument types
• Is there a way to disable function overloading in Postgres