无法选择最佳候选函数.您可能需要在Postgres中添加显式类型转换

Question

我正在使用以下脚本创建函数

CREATE OR REPLACE FUNCTION public.fninsertreceipttransaction(
accountid1 integer,
customerid1 integer,
receiptid1 integer,
retailerid1 integer,
planid1 integer,
enteredat1 timestamp without time zone,
cardtype1 character varying,
last4digits1 integer,
receiptnumber1 character varying,
totalreceiptspend1 double precision,
transactiondate1 timestamp without time zone,
storeid1 integer,
title1 character varying,
message1 character varying,
enteredby1 character varying)
RETURNS typcounter
LANGUAGE 'plpgsql'
COST 100
VOLATILE 
AS $BODY$
declare 
counter typcounter;
planId1 int;
cardid1 int;
spendtargetmax1 double precision;
begin
-- insert receipt data
update tblreceipts
set
ReceiptIssuedAt = transactiondate1,
IsDownloaded = 't',
IsProcessed  = 't',
IsVerified1  = 't',
IsVerified2  = 't',
DownloadedAt = current_timestamp,
ProcessedAt = current_timestamp,
VerifiedAt1 = current_timestamp,
VerifiedAt2 = current_timestamp,
DownloadedBy = 'user1',
ProcessedBy = 'user1',
VerifiedBy1 = 'user1',
VerifiedBy2 = 'user1'
where accountId = accountId1 and receiptId = receiptId1;
-- Check if there is active plan
-- when changing plan status, update actualCompletion date
-- Also update EOD process to include receipts
-- this should change depending on plan start and end condition
-- add transaction
select cardid into cardid1 from TblAccountCards 
where accountid=accountid1
and cardtype=cardtype1
and last4digits=last4digits1;
update TblTransactions
set
RetailerId = retailerId1,
StoreId = storeid1,
TransactionAt = transactiondate1,
EnteredAt = enteredat1,
UpdatedAt = current_timestamp,
Subtotal = totalreceiptspend1,
CardId =  cardid1
where accountId = accountId1 and receiptId = receiptId1;
-- roll up transactions to plan spent
update tblcustomerplans
set currentAmountSpent = (select sum(subtotal) from TblTransactions where 
accountId = accountId1 and planId = planId1)
where 
accountId = accountId1 
and customerId = customerId1;
select spendtargetmax into spendtargetmax1 from tblcustomerplans 
where accountid=accountid1
and customerid=customerid1
and planid=planid1;
update tblcustomerplans
set status = 'MarkComplete'
where 
accountId = accountId1 
and customerId = customerId1
and planId = planId1
and currentamountspent >= spendtargetmax1;
select cast(1 as bigint) into counter;
return counter;
end
$BODY$;

并使用以下查询执行功能

DO $$ BEGIN
PERFORM fninsertreceipttransaction(31, 24, 56, 10001, 53, '2018-11-16 20:03:28', 'Mastercard', '3434', '203', 200, '2018-11-17 00:00:00', 1,
'Receipt Trnasaction', 'Transaction Successfully Processed', 'Admin');
END $$;

获取错误：

错误：函数fninsertreceipttransaction(integer，integer，integer，integer，unknown，unknow，unknow，integer，未知，整数，未知，未知(不是唯一的

我还使用静态数据回溯了所有查询

--------------------------------                          
update tblreceipts
set
ReceiptIssuedAt = '2018-11-17 00:00:00',
IsDownloaded = 't',
IsProcessed  = 't',
IsVerified1  = 't',
IsVerified2  = 't',
DownloadedAt = current_timestamp,
ProcessedAt = current_timestamp,
VerifiedAt1 = current_timestamp,
VerifiedAt2 = current_timestamp,
DownloadedBy = 'user1',
ProcessedBy = 'user1',
VerifiedBy1 = 'user1',
VerifiedBy2 = 'user1'
where accountId = 31 and receiptId = 53;
------------------------------------
select cardid from TblAccountCards 
where accountid=31
and cardtype='Mastercard'
and last4digits=3434;
---------------------------------------
update TblTransactions
set
RetailerId = 10001,
StoreId = 1,
TransactionAt = '2018-11-17 00:00:00',
EnteredAt = '2018-11-16 20:03:28',
UpdatedAt = current_timestamp,
Subtotal = 200,
CardId =  1
where accountId = 31 and receiptId = 53;
--------------------------------------------
update tblcustomerplans
set currentAmountSpent = (select sum(subtotal) from TblTransactions where 
accountId = 31 and planId = 53)
where 
accountId = 31 and customerId = 24;
------------------------------------------------------------------------
select spendtargetmax from tblcustomerplans 
where accountid=31 and customerid=24 and planid=53;
-------------------------------- 
update tblcustomerplans
set status = 'MarkComplete'
where 
accountId = 31 
and customerId = 24
and planId = 53
and currentamountspent >= 550;

但不知道为什么问题来执行功能

Answer 1

之所以会发生这种情况，是因为已经创建了两个或多个名为fninsertreceipttransaction的函数，并且在执行过程中传递了相同数量的参数，而Postgres无法确定应该调用哪一个。

为了进行说明，让我们创建2个函数。

功能1

knayak= CREATE FUNCTION myfunction(p TIMESTAMP)
knayak- RETURNS BOOLEAN AS $$
knayak$ BEGIN
knayak$         RETURN true;
knayak$ END;
knayak$ $$  LANGUAGE plpgsql;
CREATE FUNCTION

功能2

knayak=
knayak=
knayak= CREATE FUNCTION myfunction(p ) --unknown type p
knayak- RETURNS INTEGER AS $$
knayak$ BEGIN
knayak$         RETURN 1;
knayak$ END;
knayak$ $$  LANGUAGE plpgsql;
CREATE FUNCTION

现在尝试通过传递字符串来执行该函数

knayak= DO $$
knayak$ BEGIN
knayak$  PERFORM myfunction('2018-11-16 20:03:28');
knayak$ END$$;

我得到这个错误是因为Postgres不能根据我的论点在两者之间做出决定。

错误：函数myfunction(未知(不是唯一的LINE 1:SELECTmyfunction('2018-11-16 20:03:28'(^提示：无法选择最佳候选函数。您可能需要添加显式类型强制转换。查询：选择myfunction('2018-11-16 20:03:28'(上下文：PL/pgSQL函数PERFORM 的inline_code_block第3行

现在，如何知道存在哪些函数？

如果使用psql命令提示符，请运行此简单命令。

knayak=# df myfunction
List of functions
Schema |    Name    | Result data type |      Argument data types      |  Type
--------+------------+------------------+-------------------------------+--------
public | myfunction | integer          | p                             | normal
public | myfunction | boolean          | p timestamp without time zone | normal
(2 rows)

您可以看到，存在两个具有不同参数的函数。

若您正在使用PgAdmin，运行此查询应该会得到相同的结果。

SELECT n.nspname as "Schema",
p.proname as "Name",
pg_catalog.pg_get_function_result(p.oid) as "Result data type",
pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
CASE
WHEN p.proisagg THEN 'agg'
WHEN p.proiswindow THEN 'window'
WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.proname OPERATOR(pg_catalog.~) '^(myfunction)$'
AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;

应该如何删除其中一个函数？好吧，如果你确定你只想使用一个函数，并且你有它的创建函数脚本，删除所有并重新创建它。

您不能简单地发布DROP function <functionname>，它在这种情况下不起作用。您必须指定参数签名。

knayak=# DROP function myfunction; --Doesn't work
ERROR:  function name "myfunction" is not unique
HINT:  Specify the argument list to select the function unambiguously.

这些语句有效。

knayak=#
knayak=# DROP function myfunction(p);
DROP FUNCTION
knayak=# DROP function myfunction(timestamp);
DROP FUNCTION

删除后，只重新运行create function脚本一次。它应该执行得很好。