sql - How to add number of business days to given date

Question

I'm looking for a function which adds number of business days to given date.

Holidays table

create table pyha (pyha date primary key) ;
insert into pyha values ('2018-12-24'),('2018-12-25'),('2018-12-26'),('2019-01-01');

contains holidays. Also saturday and sunday are non-business days.

I tried to create function

create or replace function add_business_day(from_date date, num_days int)
returns date
as $fbd$

with days as
(
    select dd, extract(DOW from dd) dw
    from generate_series(($1+ interval'1day')::date, ($1+ interval'1day'*$2+interval'10days')::date , '1 day'::interval) dd
)
select min(dd)::date
from   days
where  dw not in (6,0) and
dd not in (select pyha from pyha)
and dd>=$1+interval'1day'*$2+
interval'1day'*(select count(*) from pyha where pyha between $1+ interval'1day' and 
   $1+interval'1day'*$2 )
$fbd$ language sql;

But it returns sometimes incorrect result:

add_business_day('2018-12-08',2)  

returns 2018-12-10
but correct result is 2018-12-11

How to create such function in Postgres 9.1+ ?

Answer 1

The key is to generate series of business days and number them with row_number():

create or replace function add_business_day(from_date date, num_days int)
returns date
as $fbd$
    select d
    from (
        select d::date, row_number() over (order by d)
        from generate_series(from_date+ 1, from_date+ num_days* 2+ 5, '1d') d
        where 
            extract('dow' from d) not in (0, 6) 
            and d not in (select pyha from pyha)
        ) s
    where row_number = num_days
$fbd$ language sql;

The test query's results seem correct:

select days, add_business_day('2018-12-08', days)
from generate_series(1, 20) days

 days | add_business_day 
------+------------------
    1 | 2018-12-10
    2 | 2018-12-11
    3 | 2018-12-12
    4 | 2018-12-13
    5 | 2018-12-14
    6 | 2018-12-17
    7 | 2018-12-18
    8 | 2018-12-19
    9 | 2018-12-20
   10 | 2018-12-21
   11 | 2018-12-27
   12 | 2018-12-28
   13 | 2018-12-31
   14 | 2019-01-02
   15 | 2019-01-03
   16 | 2019-01-04
   17 | 2019-01-07
   18 | 2019-01-08
   19 | 2019-01-09
   20 | 2019-01-10
(20 rows)

Alternatively, you can find the date in a loop:

create or replace function add_business_day_loop(from_date date, num_days int)
returns date
as $fbd$
begin
    while num_days > 0 loop
        from_date:= from_date+ 1;
        while from_date in (select pyha from pyha) or extract('dow' from from_date) in (0, 6) loop
            from_date:= from_date+ 1;
        end loop;
        num_days:= num_days- 1;
    end loop;
    return from_date;
end;
$fbd$ language plpgsql;