I am using the following script to create function
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$;
And Executing the function with following query
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 $$;
Getting Error:
ERROR: function fninsertreceipttransaction(integer, integer, integer, integer, integer, unknown, unknown, unknown, unknown, integer, unknown, integer, unknown, unknown, unknown) is not unique
I had also back track all queries using static data
--------------------------------
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;
But Don't know why the Problem comes executing the function
This is happening because you have two or more functions already created with the name fninsertreceipttransaction and same number of arguments as passed during execution and Postgres is unable to determine which one should be called.
To illustrate, let's create 2 functions.
Function1
knayak= CREATE FUNCTION myfunction(p TIMESTAMP)
knayak- RETURNS BOOLEAN AS $$
knayak$ BEGIN
knayak$ RETURN true;
knayak$ END;
knayak$ $$ LANGUAGE plpgsql;
CREATE FUNCTION
function2
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
Now try to execute the function by passing a string
knayak= DO $$
knayak$ BEGIN
knayak$ PERFORM myfunction('2018-11-16 20:03:28');
knayak$ END$$;
I get this error because Postgres can't decide between the two based on my arguments.
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
Now, how to know which functions are present ?
If you are using psql command prompt, run this simple command.
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)
You can see that there are two functions present with different arguments.
If you are using PgAdmin, running this query should give you same result.
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;
How should you drop one of the functions? Well, If you are sure you want only one function should be used and you have it's create function script, drop all and recreate it.
You cannot simply issue DROP function <functionname> which doesn't work in such cases. You'll have to specify the argument signature.
knayak=# DROP function myfunction; --Doesn't work
ERROR: function name "myfunction" is not unique
HINT: Specify the argument list to select the function unambiguously.
These statement work.
knayak=#
knayak=# DROP function myfunction(p);
DROP FUNCTION
knayak=# DROP function myfunction(timestamp);
DROP FUNCTION
After dropping, rerun the create function script only once. It should execute fine.