For some reason since forever I wanted to write business day calculating routines. Today I took the time to do so. I've not "compiled" nor tested them, but they're a start. Do we have a corner in the repository to store these kinds of half-done, well meant contributions?
The idea is to have one table with holiday dates for separate calendars. I imagine different regions using their own calendars. For ultimate localization, the function lsmb__is_workday could be changed to query a table which lists all non-workdays (to minimize the size of the table) -- this could also be region based. I've yet to decide if this should be part of the same calendar or not. It probably should, but that's a gut feeling now.
Erik.
create or replace
function lsmb__is_workday(in_date date)
returns boolean
language sql
as $$
select extract(isodow from $1) <= 5;
$$;
create or replace
function lsmb__adjust_to_next_workday(in_date date)
returns date
language pgplsql
as $$
declare
t_date date;
begin
t_date := in_date;
loop
exit when lsmb__is_workday(t_date);
t_date := t_date + 1;
end loop;
return t_date;
end
$$;
create or replace
function lsmb__is_holiday(in_date date, in_calendar integer)
returns boolean
language plpgsql
as $$
if exists(select *
from holidays
where holidate = in_date
and calendar = in_calendar) then
return true;
else
return false;
end if;
end;
$$
create or replace
function lsmb__next_business_day(in_date date, in_calendar integer,
in_direction integer)
returns date
language pgplsql
as $$
declare
t_date date;
begin
t_date := in_date;
loop
exit when (not lsmb__is_workday(t_date, in_calendar))
and (not lsmb__is_holiday(t_date, in_calendar));
t_date := t_date + in_direction;
end loop;
return t_date;
end;
$$;
create or replace
function lsmb__next_business_day_modified(in_date date, in_calendar integer,
in_direction integer)
returns date
language pgplsql
as $$
declare
t_date date;
begin
select lsmb__next_business_day(in_date, in_calendar, in_direction)
into t_date;
if extract(month from in_date) != extract(month from t_date) then
select lsmb__next_business_day(in_date, in_calendar, -1 * in_direction)
into t_date;
end if;
return t_date;
end;
$$;
create or replace
function lsmb__closest_business_day(in_date date, in_calendar integer,
in_type integer)
returns date
language pgplsql
as $$
declare
t_date date;
begin
if in_type = 1 then -- no adjustment
t_date := in_date;
elseif in_type = 2 then -- following
select lsmb__next_business_day(in_date, in_calendar, 1)
into t_date;
elseif in_type = 3 then -- modified following
select lsmb__next_business_day_modified(in_date, in_calendar, 1)
into t_date;
elseif in_type = 4 then -- previous
select lsmb__next_businss_day(in_date, in_calendar, -1)
into t_date;
elseif in_type = 5 then -- modified previous
select lsmb__next_business_day_modified(in_date, in_calendar, -1)
into t_date;
else
raise <some error>
end if;
return t_date;
end;
$$;