在PL/pgSQL中计算两个日期间工作日数报错求助

嘿，我来帮你搞定PL/pgSQL计算工作日数的问题！先聊聊高频踩坑的错误场景和对应的修复方案，毕竟这类需求的问题大多集中在语法、逻辑或性能上：

1. 基础语法错误（最容易中招） #

新手常犯的就是漏写分号、变量定义不规范，比如下面的错误代码：

CREATE OR REPLACE FUNCTION count_weekdays(start_date DATE, end_date DATE)
RETURNS INTEGER AS $$
DECLARE
    total_days INTEGER
    weekdays INTEGER := 0;
BEGIN
    FOR i IN 0..(end_date - start_date) LOOP
        IF EXTRACT(DOW FROM start_date + i) NOT IN (0, 6) THEN
            weekdays := weekdays + 1;
        END IF;
    END LOOP;
    RETURN weekdays;
END;
$$ LANGUAGE plpgsql;

这里total_days INTEGER后面没加分号，直接触发语法报错。修复方法很简单：补充分号，删掉没用的变量（如果不需要的话）：

CREATE OR REPLACE FUNCTION count_weekdays(start_date DATE, end_date DATE)
RETURNS INTEGER AS $$
DECLARE
    weekdays INTEGER := 0;
BEGIN
    FOR i IN 0..(end_date - start_date) LOOP
        IF EXTRACT(DOW FROM start_date + i) NOT IN (0, 6) THEN
            weekdays := weekdays + 1;
        END IF;
    END LOOP;
    RETURN weekdays;
END;
$$ LANGUAGE plpgsql;

2. 日期顺序逻辑漏洞 #

如果传入的end_date早于start_date，上面的循环会因为0..负数的无效范围报错。这时候要先统一日期顺序：

CREATE OR REPLACE FUNCTION count_weekdays(start_date DATE, end_date DATE)
RETURNS INTEGER AS $$
DECLARE
    actual_start DATE;
    actual_end DATE;
    weekdays INTEGER := 0;
BEGIN
    -- 强制保证开始日期 <= 结束日期
    actual_start := LEAST(start_date, end_date);
    actual_end := GREATEST(start_date, end_date);
    
    FOR i IN 0..(actual_end - actual_start) LOOP
        IF EXTRACT(DOW FROM actual_start + i) NOT IN (0, 6) THEN
            weekdays := weekdays + 1;
        END IF;
    END LOOP;
    
    RETURN weekdays;
END;
$$ LANGUAGE plpgsql;

3. 未考虑节假日（需求常遗漏点） #

如果你的需求是排除法定节假日，仅靠周末判断就不够了。假设你有一张holidays表（含holiday_date DATE字段），可以修改函数关联判断：

CREATE OR REPLACE FUNCTION count_weekdays(start_date DATE, end_date DATE)
RETURNS INTEGER AS $$
DECLARE
    actual_start DATE;
    actual_end DATE;
    weekdays INTEGER := 0;
    current_date DATE;
BEGIN
    actual_start := LEAST(start_date, end_date);
    actual_end := GREATEST(start_date, end_date);
    
    current_date := actual_start;
    WHILE current_date <= actual_end LOOP
        -- 同时排除周末和节假日
        IF EXTRACT(DOW FROM current_date) NOT IN (0, 6)
           AND NOT EXISTS (SELECT 1 FROM holidays WHERE holiday_date = current_date) THEN
            weekdays := weekdays + 1;
        END IF;
        current_date := current_date + INTERVAL '1 day'::DATE;
    END LOOP;
    
    RETURN weekdays;
END;
$$ LANGUAGE plpgsql;

4. 大日期范围的性能问题 #

如果日期跨度是几年，循环会非常慢。可以用数学公式优化，减少循环次数：

CREATE OR REPLACE FUNCTION count_weekdays(start_date DATE, end_date DATE)
RETURNS INTEGER AS $$
DECLARE
    actual_start DATE;
    actual_end DATE;
    total_days INTEGER;
    full_weeks INTEGER;
    remaining_days INTEGER;
    weekdays INTEGER;
BEGIN
    actual_start := LEAST(start_date, end_date);
    actual_end := GREATEST(start_date, end_date);
    
    total_days := actual_end - actual_start + 1;
    full_weeks := total_days / 7;
    remaining_days := total_days % 7;
    
    weekdays := full_weeks * 5;
    
    -- 只循环处理剩余的零散天数
    FOR i IN 0..remaining_days - 1 LOOP
        IF EXTRACT(DOW FROM actual_start + i) NOT IN (0, 6) THEN
            weekdays := weekdays + 1;
        END IF;
    END LOOP;
    
    RETURN weekdays;
END;
$$ LANGUAGE plpgsql;

内容的提问来源于stack exchange，提问作者Echchama Nayak