Hi, I have written a procedure with multiple input parameters but while calling the procedure, i am facing the error.
Procedure:
CREATE OR REPLACE PROCEDURE public.sp_create_svc_accounts(
IN orgid integer,
IN bggroupid integer,
IN vclabel character varying,
IN traffictype character varying,
IN channelcode character varying,
IN protocol character varying,
IN jsvcconfigout json,
IN jsvcconfigconv json,
IN transtype character varying,
IN AccType character varying,
IN SenderID character varying[],
IN status smallint
)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
new_svcaccid integer;
array_in character varying[]:= array[SenderID];
var character varying;
BEGIN
If TransType = 'MT'
then
INSERT INTO public."ENT_SVC_ACCOUNTS"("iORGID","iBGroupID","vc Label","vcTrafficType","vcChannelCode","vcProtocol ","jSVCConfigOut","vcTransType","vcAccType")
values (ORGID,BGGroupID,vcLabel,TrafficType,ChannelCode,P rotocol,JSVCConfigOut,TransType,AccType) returning "iSVCAccountID" into new_svcaccid;
else
INSERT INTO public."ENT_SVC_ACCOUNTS"("iORGID","iBGroupID","vc Label","vcTrafficType","vcChannelCode","vcProtocol ","jSVCConfigConv","vcTransType")
values (ORGID,BGGroupID,vcLabel,TrafficType,ChannelCode,P rotocol,JSVCConfigConv,TransType,AccType) returning "iSVCAccountID" into new_svcaccid;
END IF;
update public."ENT_SVC_ACCOUNTS" set "vcSVCID"=concat(new_svcaccid,trunc( extract(epoch from CURRENT_TIMESTAMP)*1000)) where "iSVCAccountID"=new_svcaccid;
foreach var slice 1 in ARRAY array_in
loop
INSERT INTO public."ENT_SVCACC_SENDERID_MAP"("iORGID","iAccoun tID","vcSenderID","iStatus")
values(orgid,new_svcaccid,var,status);
end loop;
commit;
end
$BODY$;
Call:
call sp_create_svc_accounts(99999,9,'WA9_DEMO9_SVC9','P ROMO','WAPP','HTTP','{"iTPS": 20,"FBRoute": 3,"vcIPList": "0.0.0.0","DRSubType": "","iMaxRetry": 2,"FBClientID": "","bIsEncrypt": true,"DRNotifyURL": "http://localhost:8080/webhook","EVNotifyURL": "http://localhost:8080/webhook","FBAccountID": 0,"MONotifyURL": "http://localhost:8080/webhook","bIsFallback": true,"iMaxCBRetry": 1,"DRNotifyType": "cb","bIsProfCheck": false,"FBChannelCode": "","bIsSaasEnabled": true,"iMaxConnections": 10}',null,'MT','POSTPAID','{test,defaultGroup}',1) ;
Error:
ERROR: procedure sp_create_svc_accounts(integer, integer, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, unknown, integer) does not exist
LINE 1: call sp_create_svc_accounts(9999,9,'WA9_DEMO9_SVC9','PR OMO',...
^
HINT: No procedure matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 6