在SQL Server存储过程的WHERE子句中，我可以这样做：
WHERE (*column_value* = @some_parameter OR @some_parameter IS NULL)
当我尝试在PostgreSQL函数中做同样的事情时，它会抛出一个错误。例如：
WHERE(FRQ.QuoteId = p_FilterQuote OR p_FilterQuote IS NULL)
产生错误： ***列"p_FilterQuote"不存在。
输入参数 p_FilterQuote 在函数的顶部声明和初始化，如下所示：
p_filterquote integer DEFAULT NULL::integer
必须有一种方法可以在 WHERE 子句中"使用"输入参数。
整个函数代码如下所示。
-- FUNCTION: public.postgres_termpositionrawdata_ver3(character varying, date, character, character varying, character varying, character varying, character varying, character varying, integer, integer, character varying, character varying, character varying, character varying, character varying)
-- DROP FUNCTION public.postgres_termpositionrawdata_ver3(character varying, date, character, character varying, character varying, character varying, character varying, character varying, integer, integer, character varying, character varying, character varying, character varying, character varying);
CREATE OR REPLACE FUNCTION public.postgres_termpositionrawdata_ver3(
p_provider character varying,
p_date date,
p_correlationid character DEFAULT NULL::bpchar,
p_type character varying DEFAULT NULL::character varying,
p_productexclusionset character varying DEFAULT 'LevelTermPosition'::character varying,
p_ctmoptions character varying DEFAULT 'WOM'::character varying,
p_test character varying DEFAULT 'No'::character varying,
p_testaccountlogon character varying DEFAULT NULL::character varying,
p_averagetopx integer DEFAULT 5,
p_filterquote integer DEFAULT NULL::integer,
p_includeduplicatequotes character varying DEFAULT 'Yes'::character varying,
p_ignoreglobalpermissions character varying DEFAULT 'Yes'::character varying,
p_ignoredefaultproductexclusions character varying DEFAULT 'No'::character varying,
p_ignorelowstartresponseindicator character varying DEFAULT 'No'::character varying,
p_ignorevariableresponseindicator character varying DEFAULT 'No'::character varying)
RETURNS void
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
--DECLARE CONSTANTS
DECLARE
--DECLARE VARIABLES
v_Status varchar(10) = 'Success';BEGIN
-- assume success
-- SET CorrelationID
IF p_CorrelationId IS NULL THEN p_CorrelationId := public.swf_newid();
END IF;
DROP TABLE IF EXISTS FilteredRequests;
DROP TABLE IF EXISTS FilteredResponses;
DROP TABLE IF EXISTS Ranks;
CREATE TEMP TABLE FilteredRequests AS
SELECT * FROM public.dblink('srv_exchangemart', '
SELECT
FRQ.QuoteID
,DEO.Name AS OrganisationName
,DEO.Postcode AS OrganisationPostcode
,DEO.FRN AS OrganisationFRN
,DDQ.Date AS RequestDate
,DLB.LifeBasis
,DDC.Date AS CommencementDate
,DQF.QuotationFor
,LCR.LifeCriticalIllnessRiskRelationship
,TDP.TotalPermanentDisabilityCover
,CB.CommissionBasis
,DCT.CommissionType
,DG1.Gender AS Life1Gender
FROM FactRequest FRQ
INNER JOIN dimExchangeOrganisation DEO ON FRQ.ExchangeOrganisationID = DEO.ExchangeOrganisationId
INNER JOIN dimExchangeUser DEU ON FRQ.ExchangeUserId = DEU.ExchangeUserId
INNER JOIN dimLifeBasis DLB ON FRQ.LifeBasisId = DLB.LifeBasisID
INNER JOIN dimTotalPermanentDisabilityCover TDP ON FRQ.TotalPermanentDisabilityCoverId = TDP.TotalPermanentDisabilityCoverID
INNER JOIN dimDate DDQ ON FRQ.QuotationDateID = DDQ.DateID
INNER JOIN dimDate DDC ON FRQ.CommencementDateID = DDC.DateID
INNER JOIN dimExchangePanel DEP ON FRQ.ExchangePanelId = DEP.ExchangePanelId
INNER JOIN dimCommissionBasis CB ON FRQ.CommissionBasisId = CB.CommissionBasisId
INNER JOIN dimCommissionType DCT ON FRQ.CommissionTypeId = DCT.CommissionTypeId
INNER JOIN factClient FC ON FRQ.QuoteID = FC.QuoteID
INNER JOIN dimLifeCriticalIllnessRiskRelationship LCR ON FRQ.LifeCriticalIllnessRiskRelationshipId = LCR.LifeCriticalIllnessRiskRelationshipId
INNER JOIN dimQuotationFor DQF ON FRQ.QuotationForId = DQF.QuotationForId
INNER JOIN dimKeyPerson DKP ON FRQ.KeyPersonId = DKP.KeyPersonId
INNER JOIN dimBenefitBasis DBB ON FRQ.BenefitBasisId = DBB.BenefitBasisId
INNER JOIN dimIntegrator DI ON FRQ.ExchangeIntegratorId = DI.ExchangeIntegratorId
INNER JOIN dimGender DG1 ON FC.Life1GenderID = DG1.GenderID
INNER JOIN dimGender DG2 ON FC.Life2GenderID = DG2.GenderID
INNER JOIN dimSmoker DS1 ON FC.Life1SmokerID = DS1.SmokerID
INNER JOIN dimSmoker DS2 ON FC.Life2SmokerID = DS2.SmokerID
INNER JOIN dimOccupation DO1 ON FC.Life1OccupationId = DO1.OccupationID
INNER JOIN dimOccupation DO2 ON FC.Life2OccupationId = DO2.OccupationID
WHERE DDQ.Date = p_date
AND FRQ.ProductTypeId IN (53) -- TERM ONLY
AND DKP.KeyPerson = ''No'' -- Not Business
AND DBB.BenefitBasis = ''Benefit Led'' -- ONLY RETURNS BENEFIT LED QUOTES
AND (DEU.LogonId = p_TestAccountLogon OR (p_TestAccountLogon IS NULL
AND DI.DefaultExclusion = ''No''
AND DEO.DefaultExclusion = ''No''))
AND ( (p_IncludeDuplicateQuotes = ''No'' AND FRQ.IsDuplicateQuote = 0)
OR (p_IncludeDuplicateQuotes = ''Yes''))
AND (FRQ.QuoteId = p_FilterQuote OR p_FilterQuote IS NULL)
AND ( (p_CTMOptions = ''CTM Only''AND DEU.LogonId = ''CTM000'')
OR (p_CTMOptions = ''Exclude CTM'' AND DEU.LogonId != ''CTM000'')
OR (p_CTMOptions = ''WOM'')
)
')
AS DATA
(QuoteID integer, OrganisationName character varying, OrganisationPostcode character varying, OrganisationFRN character varying, RequestDate date,
LifeBasis character varying, CommencementDate date, QuotationCoverFor character varying,
LifeCIRiskRelationship character varying, TPDOption character varying, CommissionBasis character varying,
CommissionType character varying, Life1Gender character varying);
-- SELECT * FROM FilteredRequests;
END;
$BODY$;
SQL 语句按在远程数据库上的原样执行，不执行任何类型的参数替换。
你必须自己做：
DECLARE
sql text;
BEGIN
sql := format(
'SELECT ... WHERE(FRQ.QuoteId = %L::integer OR %L::integer IS NULL)',
p_FilterQuote,
p_FilterQuote
);
SELECT * FROM dblink('conn', sql) AS ...;
END;