c# - 无法选择最佳候选函数。您可能需要在 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 $$;

获取错误:

ERROR: function fninsertreceipttransaction(integer, integer, integer, integer, integer, unknown, unknown, unknown, unknown, integer, unknown, integer, unknown, unknown, unknown) is not unique

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

                --------------------------------                          
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 无法根据我的论点在两者之间做出决定。

ERROR: function myfunction(unknown) is not unique LINE 1: SELECT myfunction('2018-11-16 20:03:28') ^ HINT: Could not choose a best candidate function. You might need to add explicit type casts. QUERY: SELECT myfunction('2018-11-16 20:03:28') CONTEXT: PL/pgSQL function inline_code_block line 3 at PERFORM

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

如果您使用 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脚本只有一次。它应该可以正常执行。