Getting this error when calling a stored proc in a Postgres database. We migrated from MySQL and this was working fine until we switched to Postgres. Any ideas? <td>ERROR: function dbo.spgetaoid(double precision, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying) does not exist<br> Hint: No function matches the given name and argument types. You might need to add explicit type casts.<br> Position: 15</td> THIS IS THE CALL IN CFCODE: <cfstoredproc procedure="dbo.spGetAOID" datasource="#session.dsn#"> <cfprocparam cfsqltype="cf_sql_numeric" value="#arguments.aostruct.clientID#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.subjectID#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.packageID#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.clientOrderID#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.resultAddress#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.customReportName#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.webBannerGraphic#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.reportGraphic#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.postProcessType#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.postProcessURL#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" value="#arguments.aostruct.additionalScales#"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.aoid"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.status"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.hashedAOID"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.assessmentURL"> <cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="this.participantID"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.languageUsed"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.resultAddress"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.resultProtocol"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.customReportName"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.reportType"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.jobCategoryTitle"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.webBannerGraphic"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.reportGraphic"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.postProcessType"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.postProcessURL"> <cfprocparam cfsqltype="CF_SQL_VARCHAR" type="out" variable="this.additionalScales"> <cfprocparam cfsqltype="cf_sql_numeric" type="out" variable="this.isAssessment"> </cfstoredproc> THIS IS STORED PROC IN POSTGRES DB -- ============================================= -- Author: Evan Lee -- Create date: 12/11/2008 -- Description: See if AO already exists for the assessments needed in this AOR. If it does, return the existing AOID, if not create new AOR -- ============================================= CREATE OR REPLACE FUNCTION dbo.spGetAOID ( varg_clientID int, varg_subjectID varchar(50), varg_packageID varchar(15), varg_clientOrderID varchar(50), varg_resultAddress varchar(250), varg_customReportName varchar(150), varg_webBannerGraphic varchar(250), varg_reportGraphic varchar(250), varg_redirectURLType varchar(10), varg_redirectURL varchar(500), varg_additionalScales varchar(25), out vaoid int, out vstatus varchar(15), out vhashedAOID varchar(100), out vassessmentURL varchar(250), out vparticipantID int, out vlanguageUsed varchar(10), out vresultAddress varchar(250), out vresultProtocol varchar(10), out vcustomReportName varchar(150), out vreportType varchar(150), out vjobCategoryTitle varchar(150), out vwebBannerGraphic varchar(250), out vreportGraphic varchar(250), out vredirectURLType varchar(10), out vredirectURL varchar(500), out vadditionalScales varchar(25), out visAssessment boolean ) RETURNS VOID AS $$ BEGIN SELECT r.reportType INTO @reportType FROM packageID p INNER JOIN cg.ReportType r ON p.ReportTypeID = r.ReportTypeID WHERE p.packageID = varg_packageID; SELECT jobCategoryTitle, isAssessment INTO vjobCategoryTitle, visAssessment FROM JobCategory WHERE jobCategoryID = LEFT(varg_packageID, POSITION('.' IN varg_packageID) - 1); IF exists (SELECT aoid FROM am.AO WHERE ClientID = varg_clientID AND subjectID = varg_subjectID AND packageID = varg_packageID) THEN /* There is an existing Order for the same packageID for the same person, but we do update the: 1. resultAddress incase they want it to sent in a new address 2. reportGraphic. */ UPDATE am.AO SET clientOrderID = varg_clientOrderID, resultAddress = varg_resultAddress, ReportGraphic = varg_reportGraphic, WebBannerGraphic = varg_webBannerGraphic, LastTimeOrdered = CURRENT_TIMESTAMP WHERE ClientID = varg_clientID AND subjectID = varg_subjectID AND packageID = varg_packageID; -- we then get the existing AOID, and etc for the existing order, and use it. Hence we don't have to create a new order each time. SELECT aoid, status, hashedAOID, assessmentURL, participantID, languageUsed, resultAddress, resultProtocol, customReportName, WebBannerGraphic, ReportGraphic, RedirectURLType, RedirectURL, AdditionalAttitudeScales INTO vaoid, vstatus, vhashedAOID, vassessmentURL, vparticipantID, vlanguageUsed, vresultAddress, vresultProtocol, vcustomReportName, vwebBannerGraphic, vreportGraphic, vredirectURLType, vredirectURL, vadditionalScales FROM am.AO WHERE ClientID = varg_clientID AND subjectID = varg_subjectID AND packageID = varg_packageID; ELSE DECLARE v_arg_resultProtocol varchar(10); SELECT resultProtocol INTO v_arg_resultProtocol FROM clientDetail WHERE ClientID = varg_clientID; INSERT INTO am.AO (clientID,subjectID,packageID,resultAddress,customReportName,resultProtocol, status, OrderCreationDate) VALUES (varg_clientID,varg_subjectID,varg_packageID, varg_resultAddress, varg_customReportName, v_arg_resultProtocol, 'Acknowledged', CURRENT_TIMESTAMP); IF exists (SELECT TOP 1 participantID, languageUsed FROM am.AO WHERE ClientID = varg_clientID AND subjectID = varg_subjectID ORDER BY aoid DESC) THEN SELECT TOP 1 participantID, languageUsed INTO vparticipantID, vlanguageUsed FROM am.AO WHERE ClientID = varg_clientID AND subjectID = varg_subjectID ORDER BY aoid; END IF; vaoid := @@IDENTITY; vhashedAOID := NULL; vassessmentURL := NULL; vstatus := 'Acknowledged'; vresultAddress := varg_resultAddress; vresultProtocol := v_arg_resultProtocol; vcustomReportName := varg_customReportName; vwebBannerGraphic := varg_webBannerGraphic; vreportGraphic := varg_reportGraphic; vredirectURLType := varg_redirectURLType; vredirectURL := varg_redirectURL; vadditionalScales := varg_additionalScales; END IF; END; $$ LANGUAGE plpgsql;

@mauzer please use threads and codeblocks. Also, don't crosspost the same question in multiple channels.

👍

Personally, I don't use cfstoredproc in postgres, I just call the funtion in a cfquery