PostgreSQL中逾期天数小于3天的计数返回异常求助

我正在使用PostgreSQL的dvdrental数据库，想要编写一个函数返回每个客户逾期天数小于3天的次数，但当前函数每行都返回6，推测是取到了第一个客户的计数。

示例数据 #

预期结果 #

每个customer_id对应的行返回该客户逾期天数<3的总次数：

当前函数实现 #

create or replace function pd_lt3(customer_id varchar(45),days_past_due smallint)
returns smallint
language plpgsql
as $$
declare pd_lt3 smallint;
begin
    
    select total from(
    select customer_id,count(*) as total from past_due_detail where days_past_due < 3 group by customer_id
        )
    into pd_lt3;
    return pd_lt3;
end;
$$;

更多背景（以下内容均可正常运行） #

逾期明细记录表创建语句 #

CREATE TABLE past_due_detail(   
    customer_id integer not null,
    first_name varchar not null,
    last_name varchar not null,
    rental_id integer not null,
    rental_date timestamp not null,
    return_date timestamp,
    title varchar not null,
    days_past_due smallint,
    primary key (rental_id),
    foreign key (customer_id) references customer(customer_id)
);

计算逾期天数的函数 #

CREATE OR REPLACE FUNCTION public.days_past_due(
    rental_date timestamp without time zone,
    return_date timestamp without time zone,
    rental_duration smallint,
    last_update timestamp without time zone)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
declare days_past_due int;
begin
    select ((cast(return_date as date)) - (cast(rental_date as date)) - rental_duration) where ((cast(return_date as date)) - (cast(rental_date as date)) - rental_duration) > 0 and return_date is not null
        into days_past_due;
    return days_past_due;
    select ((cast(last_update as date)) - (cast(rental_date as date)) - rental_duration) where ((cast(last_update as date)) - (cast(return_date as date)) - rental_duration) > 0 and return_date is null
        into days_past_due;
    return days_past_due;
end;
$BODY$;

填充past_due_detail表的语句 #

insert into past_due_detail(
select rental.customer_id,first_name,last_name,rental_id,rental_date,return_date,title,days_past_due(rental_date,return_date,rental_duration,rental.last_update)
from inventory
inner join film on film.film_id = inventory.film_id
inner join rental on rental.inventory_id = inventory.inventory_id
inner join customer on rental.customer_id = customer.customer_id
where days_past_due(rental_date,return_date,rental_duration,rental.last_update) > 0
order by rental_date,rental.customer_id
);

问题分析与修复 #

你的函数存在两个核心问题：

1. 未筛选当前客户：子查询统计了所有客户的逾期<3次数，且仅取第一行结果，导致所有调用返回固定值。
2. 参数类型不匹配：函数参数customer_id定义为varchar(45)，但表中该字段是integer类型，会引发类型转换异常。

修复后的PL/pgSQL函数 #

create or replace function pd_lt3(p_customer_id integer)
returns smallint
language plpgsql
as $$
declare pd_count smallint;
begin
    -- 仅统计传入客户的逾期天数<3的记录数
    select count(*)
    into pd_count
    from past_due_detail 
    where customer_id = p_customer_id 
      and days_past_due < 3;
    
    return pd_count;
end;
$$;

更简洁的SQL函数版本 #

无需PL/pgSQL，直接用SQL函数实现：

create or replace function pd_lt3(p_customer_id integer)
returns smallint
language sql
as $$
    select count(*)::smallint
    from past_due_detail 
    where customer_id = p_customer_id 
      and days_past_due < 3;
$$;

使用示例 #

查询时每行返回对应客户的逾期次数：

select 
    customer_id,
    pd_lt3(customer_id) as days_past_due_count
from past_due_detail;

内容的提问来源于stack exchange，提问作者John Crosby