PostgreSQL -如何在例程中使用等于null

Question

我有一个传入4个参数的例程。有时最后一个参数"membership_type "是null，但当我使用等号时，它不会返回null值的记录，如果我使用"is null"但是如何确保两者在同一个例程中工作呢?

create or replace function fitnessone_master.get_location_cancellation_count(club_number double precision, date date, member_profile character varying, membership_type character varying) returns integer
language plpgsql
as $$
declare
member_count integer;
begin
return (select count(distinct a.agreement_number) as member_count
from fitnessone_master.cancelled_accounts a
where cancel_date = get_location_cancellation_count.date
and a.club_number = get_location_cancellation_count.club_number
and a.member_profile = get_location_cancellation_count.member_profile
and a.membership_type = get_location_cancellation_count.membership_type); 
end;
$$;

Answer 1

不要使用永远无法匹配NULL的= x，您可以使用IS NOT DISTINCT FROM x:

create or replace function fitnessone_master.get_location_cancellation_count(club_number double precision, date date, member_profile character varying, membership_type character varying) returns integer
language plpgsql
as $$
declare
member_count integer;
begin
return (select count(distinct a.agreement_number) as member_count
from fitnessone_master.cancelled_accounts a
where cancel_date = get_location_cancellation_count.date
and a.club_number = get_location_cancellation_count.club_number
and a.member_profile = get_location_cancellation_count.member_profile
and a.membership_type is not distinct from get_location_cancellation_count.membership_type); 
end;
$$;